tabl1
create table tbs
(
pono varchar(10),
pno varchar(10),
dpt varchar(10),
qty int
)
go
insert into tbs(pono,pno,dpt,qty)values('PP001','ITEM001','RS',100)
insert into tbs(pono,pno,dpt,qty)values('PP002','ITEM026','RS',100)
insert into tbs(pono,pno,dpt,qty)values('PP003','ITEM033','IT',100)
insert into tbs(pono,pno,dpt,qty)values('PP003','ITEM286','IT',100)
insert into tbs(pono,pno,dpt,qty)values('PP003','ITEM286','QA',100)
--假设有三部门,要求统计三部门各采购多少
--类似的语句
select sum(qty) RS_ORD,sum(qty) IT_ORD,sum(qty) QA_ORD from tbs ...
想要的结果：
RS_ORD IT_ORD QA_ORD
200 200 100
求高效率的sql语句
------解决方案--------------------
--create table tbs
--(
--pono varchar(10),
--pno varchar(10),
--dpt varchar(10),
--qty int
--)
--go
--insert into tbs(pono,pno,dpt,qty)values('PP001','ITEM001','RS',100)
--insert into tbs(pono,pno,dpt,qty)values('PP002','ITEM026','RS',100)
--insert into tbs(pono,pno,dpt,qty)values('PP003','ITEM033','IT',100)
--insert into tbs(pono,pno,dpt,qty)values('PP003','ITEM286','IT',100)
--insert into tbs(pono,pno,dpt,qty)values('PP003','ITEM286','QA',100)
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+dpt+'_ORD'+'=sum(case when [dpt]='+quotename(dpt,'''')+' then [qty] else 0 end)'
from tbs group by dpt
SET @s=SUBSTRING (@s,2,len(@s))
EXEC ('select '+@s+' from tbs ')
------解决方案--------------------
create table tbs
(
pono varchar(10),
pno varchar(10),
dpt varchar(10),
qty int
)
go
insert into tbs(pono,pno,dpt,qty)values('PP001','ITEM001','RS',100)
insert into tbs(pono,pno,dpt,qty)values('PP002','ITEM026','RS',100)
insert into tbs(pono,pno,dpt,qty)values('PP003','ITEM033','IT',100)
insert into tbs(pono,pno,dpt,qty)values('PP003','ITEM286','IT',100)
insert into tbs(pono,pno,dpt,qty)values('PP003','ITEM286','QA',100)
go
select
sum(case when dpt='RS' then qty else 0 end) as RS,
sum(case when dpt='IT' then qty else 0 end) as IT,
sum(case when dpt='QA' then qty else 0 end) as QA
from tbs
/*
RS IT QA
----------- ----------- -----------
200 200 100
(1 行受影响)
*/
go
drop table tbs
------解决方案--------------------
1楼的好用些，2楼的好理解些。以后版主贴代码可以不可以带上几个注释啊。。新手不太理解诶。