SELECT NAME
SUM(CASE WHEN TYPE=1 THEN 数量 END )AS TYPE1,
SUM(CASE WHEN TYPE=2 THEN 数量 END )AS TYPE2,
SUM(CASE WHEN TYPE=3 THEN 数量 END )AS TYPE3
FROM TB
GROUP BY NAME
SUM(CASE WHEN TYPE=1 THEN 数量 END )AS TYPE1,
SUM(CASE WHEN TYPE=2 THEN 数量 END )AS TYPE2,
SUM(CASE WHEN TYPE=3 THEN 数量 END )AS TYPE3
FROM TB
GROUP BY NAME
select name,
sum(case when type = 1 then num else 0 end) ['type1'],
sum(case when type = 2 then num else 0 end) ['type2'],
sum(case when type = 3 then num else 0 end) ['type3']
from test
group by name
里面有动态的!!!我对动态还不太懂哦,呵呵
select name,
sum(case when type = 1 then num else 0 end) [type1],
sum(case when type = 2 then num else 0 end) [type2],
sum(case when type = 3 then num else 0 end) [type3]
from test
group by name
INSERT INTO @test
SELECT 1,'a',1,10 UNION ALL
SELECT 2,'b',2,20 UNION ALL
SELECT 3,'c',3,30 UNION ALL
SELECT 4,'a',2,40 UNION ALL
SELECT 5,'a',1,50 UNION ALL
SELECT 6,'b',2,60
SELECT NAME,
SUM(CASE WHEN TYPE=1 THEN num END )AS TYPE1,
SUM(CASE WHEN TYPE=2 THEN num END )AS TYPE2,
SUM(CASE WHEN TYPE=3 THEN num END )AS TYPE3
FROM @test
GROUP BY NAME
修改下
SELECT NAME,
SUM(CASE WHEN TYPE=1 THEN num END )AS TYPE1,
SUM(CASE WHEN TYPE=2 THEN num END )AS TYPE2,
SUM(CASE WHEN TYPE=3 THEN num END )AS TYPE3
FROM @test
GROUP BY NAME高手高手,感谢大家这么迅速!
select name,
(select sum(num) from test t where t.type=1 group by [name]) as type1,
(select sum(num) from test t where t.type=2 group by [name]) as type2,
(select sum(num) from test t where t.type=3 group by [name]) as type3 from test t
学到CASE的用法,强大!谢谢大家~