高分急求!!!

weihouyanli 2008-11-12 04:52:39
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
...全文
169 13 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
zdgood 2008-11-12
  • 打赏
  • 举报
回复
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
ws_hgo 2008-11-12
  • 打赏
  • 举报
回复
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
dlmeijianyu 2008-11-12
  • 打赏
  • 举报
回复
有答案了,就来up下。
fcuandy 2008-11-12
  • 打赏
  • 举报
回复
网速太慢,打不开,进来接个分好了。
pengxuan 2008-11-12
  • 打赏
  • 举报
回复

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
wfqqwer5213 2008-11-12
  • 打赏
  • 举报
回复
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
csdyyr 2008-11-12
  • 打赏
  • 举报
回复
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
*/
dobear_0922 2008-11-12
  • 打赏
  • 举报
回复
---------------------------------
-- 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 行受影响)
*/
CN_SQL 2008-11-12
  • 打赏
  • 举报
回复
也可以这样:

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 2008-11-12
  • 打赏
  • 举报
回复
select Column1
, Column2=sum(Column2)
, Column3=sum(Column3)
, Column4=sum(Column4)
, Column5=sum(Column5)
from A
group by Column1
CN_SQL 2008-11-12
  • 打赏
  • 举报
回复
就是一个简单的聚合问题.
liangCK 2008-11-12
  • 打赏
  • 举报
回复
---------------------------------
-- 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 行受影响)

*/
appleller 2008-11-12
  • 打赏
  • 举报
回复
什么意思?没看懂!

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧