高分急求!!!

[weihouyanli]

Table A

Column1 Column2 Column3 Column4 Column5
049D null 15 null null
049D 16 null null null
049D null null null 16
049D null null 15 null
050D null 16 null null
050D 17 null null null
050D null null null 16
050D null null 15 null

我要查询出

TableB
Column1 Column2 Column3 Column4 Column5
049D 16 15 15 16
049D 17 16 15 16

[appleller]

什么意思?没看懂!

[liangCK]

---------------------------------
--  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 行受影响)

*/

[CN_SQL]

就是一个简单的聚合问题.

[dobear_0922]

select Column1
    ,  Column2=sum(Column2)
    ,  Column3=sum(Column3)
    ,  Column4=sum(Column4)
    ,  Column5=sum(Column5) 
from A
group by Column1

[CN_SQL]

也可以这样:

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

[dobear_0922]

---------------------------------
--  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 行受影响)
*/

[csdyyr]

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
*/

[wfqqwer5213]

select Column1,
max(nvl(Column2,0)) Column2,
max(nvl(Column3,0)) Column3,
max(nvl(Column4,0)) Column4,
max(nvl(Column5,0)) Column5 from A group by Column1

[pengxuan]

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

[fcuandy]

网速太慢，打不开，进来接个分好了。

[dlmeijianyu]

有答案了，就来up下。

[ws_hgo]

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

[zdgood]

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