34,838
社区成员




DECLARE @TABLE TABLE (COLUMN1 VARCHAR(10),COLUMN2 FLOAT,COLUMN3 FLOAT,COLUMN4 FLOAT,COLUMN5 FLOAT)
INSERT @TABLE (COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5)
SELECT '049D',0,15,0,0 UNION ALL
SELECT '049D',16,0,0,0 UNION ALL
SELECT '049D',0,0,0,16 UNION ALL
SELECT '049D',0,0,15,0 UNION ALL
SELECT '050D',0,16,0,0 UNION ALL
SELECT '050D',17,0,0,0 UNION ALL
SELECT '050D',0,0,0,16 UNION ALL
SELECT '050D',0,0,15,0
SELECT COLUMN1,SUM(COLUMN2),SUM(COLUMN3),SUM(COLUMN4),SUM(COLUMN5)
FROM @TABLE
GROUP BY COLUMN1
create TABLE #EE(Column1 VARCHAR(4),Column2 INT,Column3 INT,Column4 INT,Column5 INT)
INSERT INTO #EE
SELECT '049D',null,15,null,null UNION ALL
SELECT '049D',16,null,null,null UNION ALL
SELECT '049D',null,null,null,16 UNION ALL
SELECT '049D',null,null,15,null UNION ALL
SELECT '050D',null,16,null,null UNION ALL
SELECT '050D',17,null,null,null UNION ALL
SELECT '050D',null,null,null,16 UNION ALL
SELECT '050D',null,null,15,null
select Column1,
max(Column2) 'Column2',
max(Column3) 'Column3',
max(Column4) 'Column5',
max(Column5) 'Column5'
from #EE group by Column1
select column1,
column2=sum(isnull(column2,0)),
column3=sum(isnull(column3,0)),
column4=sum(isnull(column4,0)),
column5=sum(isnull(column5,0))
from tb group by column1
DECLARE @TB TABLE(Column1 VARCHAR(5), Column2 INT, Column3 INT, Column4 INT, Column5 INT)
INSERT @TB
SELECT '049D', null, 15, null, null UNION ALL
SELECT '049D', 16, null, null, null UNION ALL
SELECT '049D', null, null, null, 16 UNION ALL
SELECT '049D', null, null, 15, null UNION ALL
SELECT '050D', null, 16, null, null UNION ALL
SELECT '050D', 17, null, null, null UNION ALL
SELECT '050D', null, null, null, 16 UNION ALL
SELECT '050D', null, null, 15, null
SELECT Column1,SUM( Column2) AS Column2,SUM( Column3) AS Column3,SUM( Column4) AS Column4,SUM( Column5) AS Column5
FROM @TB
GROUP BY Column1
/*
Column1 Column2 Column3 Column4 Column5
------- ----------- ----------- ----------- -----------
049D 16 15 15 16
050D 17 16 15 16
*/
---------------------------------
-- Author: liangCK 小梁
-- Date : 2008-11-12 16:55:10
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (Column1 VARCHAR(4),Column2 INT,Column3 INT,Column4 INT,Column5 INT)
INSERT INTO @T
SELECT '049D',null,15,null,null UNION ALL
SELECT '049D',16,null,null,null UNION ALL
SELECT '049D',null,null,null,16 UNION ALL
SELECT '049D',null,null,15,null UNION ALL
SELECT '050D',null,16,null,null UNION ALL
SELECT '050D',17,null,null,null UNION ALL
SELECT '050D',null,null,null,16 UNION ALL
SELECT '050D',null,null,15,null
select Column1
, Column2=sum(Column2)
, Column3=sum(Column3)
, Column4=sum(Column4)
, Column5=sum(Column5)
from @T
group by Column1
/*
Column1 Column2 Column3 Column4 Column5
------- ----------- ----------- ----------- -----------
049D 16 15 15 16
050D 17 16 15 16
(2 行受影响)
*/
SELECT
Column1,
SUM(ISNULL(Column2,0)) AS Column2,
SUM(ISNULL(Column3,0)) AS Column3,
SUM(ISNULL(Column4,0)) AS Column4,
SUM(ISNULL(Column5,0)) AS Column5
FROM @T
GROUP BY Column1
select Column1
, Column2=sum(Column2)
, Column3=sum(Column3)
, Column4=sum(Column4)
, Column5=sum(Column5)
from A
group by Column1
---------------------------------
-- Author: liangCK 小梁
-- Date : 2008-11-12 16:55:10
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (Column1 VARCHAR(4),Column2 INT,Column3 INT,Column4 INT,Column5 INT)
INSERT INTO @T
SELECT '049D',null,15,null,null UNION ALL
SELECT '049D',16,null,null,null UNION ALL
SELECT '049D',null,null,null,16 UNION ALL
SELECT '049D',null,null,15,null UNION ALL
SELECT '050D',null,16,null,null UNION ALL
SELECT '050D',17,null,null,null UNION ALL
SELECT '050D',null,null,null,16 UNION ALL
SELECT '050D',null,null,15,null
--SQL查询如下:
SELECT
Column1,
MAX(Column2) AS Column2,
MAX(Column3) AS Column3,
MAX(Column4) AS Column4,
MAX(Column5) AS Column5
FROM @T
GROUP BY Column1
/*
Column1 Column2 Column3 Column4 Column5
------- ----------- ----------- ----------- -----------
049D 16 15 15 16
050D 17 16 15 16
(2 行受影响)
*/