34,576
社区成员
发帖
与我相关
我的任务
分享
select Cname,a=sum(case when left(id,4) <'1013' then num else 0 end) ,
b=sum(case when left(id,4) >'1013' then num else 0 end)
from @COMPANY a,@count c where a.CID=c.cid group by Cname
/*
Cname a b
----- ----------- -----------
公司A 35 69
公司B 28 19
*/
declare @COMPANY table (CID int,Cname varchar(5))
insert into @COMPANY
select 1,'公司A' union all
select 2,'公司B' union all
select 3,'公司C' union all
select 4,'公司D' union all
select 5,'公司E'
declare @count table (id varchar(10),cid int,NUM int)
insert into @count
select 101101,1,10 union all
select 101101,1,2 union all
select 101102,1,15 union all
select 1012,1,8 union all
select 1015,1,16 union all
select 1015,1,7 union all
select 1015,1,8 union all
select 10160101,1,9 union all
select 10160102,1,5 union all
select 10160102,1,19 union all
select 101602,1,5 union all
select 101101,2,5 union all
select 101102,2,15 union all
select 1012,2,8 union all
select 1015,2,7 union all
select 10160101,2,9 union all
select 10160102,2,0 union all
select 101602,2,3
select a.Cname,b.a,b.b from @COMPANY a left join (
select cid,
a=sum(case when left(id,4) between '1011' and '1012' then num else 0 end) ,
b=sum(case when left(id,4) between '1015' and '1016' then num else 0 end)
from @count group by cid) b on a.CID=b.cid where b.cid is not null
/*
Cname a b
----- ----------- -----------
公司A 35 69
公司B 28 19
*/
select
a.cname,
sum(case when id like '1011%' or id like '1012%' then 1 else 0 end) as a,
sum(case when id like '1015%' or id like '1016%' then 1 else 0 end) as b
from
company a,[count] b
where
a.cid=b.cid
group by
a.cname
帮顶,等高手优化语句