27,579
社区成员
发帖
与我相关
我的任务
分享
--1
--> 测试数据:@tab1
declare @tab1 table([A] varchar(3),[B] int,[C] int)
insert @tab1
select '001',11,30 union all
select '002',22,60
--> 测试数据:@tab2
declare @tab2 table([a] int,[b] int)
insert @tab2
select 11,30 union all
select 11,60 union all
select 22,70 union all
select 22,90
select d = a.a,e = a.c + isnull((select sum(b) from @tab2 b where b.a = a.b),0) from @tab1 a
/*
d e
---- -----------
001 120
002 220
(所影响的行数为 2 行)
*/
--2,静态SQL,指字段2只有aa,bb,cc
declare @tab3 table([字段1] varchar(2),[字段2] varchar(2),[字段3] int)
insert @tab3
select '01','aa',60 union all
select '01','bb',70 union all
select '01','bb',80 union all
select '01','cc',90
select 字段1 ,
sum(case 字段2 when 'aa' then 字段3 else 0 end) aa,
sum(case 字段2 when 'bb' then 字段3 else 0 end) bb,
sum(case 字段2 when 'cc' then 字段3 else 0 end) cc
from @tab3
group by 字段1
/*
字段1 aa bb cc
---- ----------- ----------- -----------
01 60 150 90
(所影响的行数为 1 行)
*/
--2,动态写法,指字段2不止aa,bb,cc
create table tab3([字段1] varchar(2),[字段2] varchar(2),[字段3] int)
insert tab3
select '01','aa',60 union all
select '01','bb',70 union all
select '01','bb',80 union all
select '01','cc',90
declare @sql varchar(8000)
set @sql = 'select 字段1 '
select @sql = @sql + ' , sum(case 字段2 when ''' + 字段2 + ''' then 字段3 else 0 end) [' + 字段2 + ']'
from (select distinct 字段2 from tab3) as a
set @sql = @sql + ' from tab3 group by 字段1'
exec(@sql)
drop table tab3
/*
字段1 aa bb cc
---- ----------- ----------- -----------
01 60 150 90
(所影响的行数为 1 行)
*/
--> 测试数据:@tab3
declare @tab3 table([字段1] varchar(2),[字段2] varchar(2),[字段3] int)
insert @tab3
select '01','aa',60 union all
select '01','bb',70 union all
select '01','bb',80 union all
select '01','cc',90
----------------------------------查询开始-------------------------------------
select [字段1],sum(case [字段2] when 'aa' then [字段3] else null end ) as [aa],
sum(case [字段2] when 'bb' then [字段3] else null end ) as [bb],
sum(case [字段2] when 'cc' then [字段3] else null end ) as [cc]
from @tab3
group by [字段1]
-----------------------------------结果-----------------------------------------
/*
字段1 aa bb cc
---- ----------- ----------- -----------
01 60 150 90
警告: 聚合或其他 SET 操作消除了空值。
(1 行受影响)
*/
第一题
--> 测试数据:@tab1
declare @tab1 table([A] varchar(3),[B] int,[C] int)
insert @tab1
select '001',11,30 union all
select '002',22,60
--> 测试数据:@tab2
declare @tab2 table([a] int,[b] int)
insert @tab2
select 11,30 union all
select 11,60 union all
select 22,70 union all
select 22,90
--------------------------查询开始----------------------------
select a.[A] as[d],sum(a.[C]+b.[b]) as[e] from @tab1 a
left join
(select [a],sum([b]) as [b] from @tab2 group by [a]) b on a.[B]=b.[a]
group by a.[A]
----------------------------------结果---------------------------------
/*
d e
---- -----------
001 120
002 220
*/