有表如下:
col1 col2 col3
a 3 usr
b 2 adm
a 5 adm
a 6 adm
c 6 usr
c 8 usr
d 9 other
要求:
col1 col2_all col2_usr col2_adm
a 3+5+6=14 3 5+6=11
b 2 0 2
c 6+8=14 6+8=14 0
d 9 0 0
先谢谢了
...全文
205打赏收藏
求解一个sql的写法
有表如下: col1 col2 col3 a 3 usr b 2 adm a 5 adm a 6 adm c 6 usr c 8 usr d 9 other 要求: col1 col2_all col2_usr col2_adm a 3+5+6=14 3 5+6=11 b 2 0 2 c 6+8=14 6+8=14 0 d 9 0 0 先谢谢了
SELECT col1, SUM(col2) AS col2_all, SUM(CASE ltrim(rtrim(col3))
WHEN 'usr' THEN col2 ELSE 0 END) AS col2_usr,
SUM(CASE col3 WHEN 'adm' THEN col2 ELSE 0 END) AS col2_adm
FROM tb1
GROUP BY col1
已测试过,没问题,结果如下。
a 14 3 11
b 2 0 2
c 14 14 0
d 9 0 0
select DISTINCT col1 ,
isnull(select sum(co12) from table a where a.co11 = co11) as co12_all,
isnull(select sum(co12) from table a where a.co13 = 'usr') as co12_usr,
isnull(select sum(co12) from table a where a.co13 = 'adm') as co12_adm
from table