求一SQL语句-----》在线等。马上揭贴`
有一张表
t_folder
case_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也没做好```
[解决办法]
select count(*) as 总数,sum(case status when '1 ' then 1 else 0 end) as 已办 from t_folder
[解决办法]
create table t_folder(case_id int,case_name char(1),status int)
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 ',3
select 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 行)
*/
[解决办法]
楼主的速度真快
[解决办法]
select count(case_id),
已办=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
[解决办法]
select count(*) as 总数,sum(case status when '1 ' then 1 else 0 end) as 已办,
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
[解决办法]
select (select count(*) from t_folder) as 总数,(select count(*) from t_folder where status=1) as 已办,(select count(*) from t_folder where status=2) as 未办理,(select count(*) from t_folder where status=3) as 转办