有一张表
t_foldercase_id case_name status
1 a 1 //已办
2 b 2 //未办理
3 c 3 //转办
4 d 1
5 e 3要求的结果 统计:如下
总数 已办 未办理 转办
5 2 1 2我用select count(*) as 总数,(select count(*) from t_folder where status=1) as 已办 from t_folder
这样不行``用inner join也没做好```
t_foldercase_id case_name status
1 a 1 //已办
2 b 2 //未办理
3 c 3 //转办
4 d 1
5 e 3要求的结果 统计:如下
总数 已办 未办理 转办
5 2 1 2我用select count(*) as 总数,(select count(*) from t_folder where status=1) as 已办 from t_folder
这样不行``用inner join也没做好```
insert into t_folder
select 1,'a',1
union all select 2,'b',2
union all select 3,'c',3
union all select 4,'d',1
union all select 5,'e',3select count(*) as '总数',
sum(case when status = 1 then 1 else 0 end) as '已办',
sum(case when status = 2 then 1 else 0 end) as '未办理',
sum(case when status = 3 then 1 else 0 end) as '转办'
from t_folder/*
总数 已办 未办理 转办
----------- ----------- ----------- -----------
5 2 1 2(所影响的行数为 1 行)
*/
已办=sum(case when status=1 then 1 else 0 end),
未办理=sum(case when status=2 then 1 else 0 end),
转办=sum(case when status=3 then 1 else 0 end)
from t_folder
sum(case status when '2' then 1 else 0 end) as 未办理,
sum(case status when '3' then 1 else 0 end) as 转办 from t_folder
sum(case when status = 1 then 1 else 0 end) as '已办',
sum(case when status = 2 then 1 else 0 end) as '未办理',
sum(case when status = 3 then 1 else 0 end) as '转办'
from t_folder---------
count(1)比count(*)效率高。
insert @Test
select 1, 'a', 1 union all
select 2, 'b', 2 union all
select 3, 'c', 3 union all
select 4, 'd', 1 union all
select 5, 'e', 3
select 总数 = sum(total), 已办 = sum(case status when 1 then total else 0 end), 未办 = sum(case status when 2 then total else 0 end), 转办 = sum(case status when 3 then total else 0 end) from
(select id = 1, status, total = count(*) from @test group by status) a
group by id