字段ID int 标示 字段USERID varchar(32)
字段STATUS tinyint 1代表“成功” 2代表“失败” 3代表“未知错误”
表
ID USERID STATUS
1 5001 1
2 5001 1
3 5001 2
4 5001 3
5 5002 1
6 5002 2
7 5002 1
8 5003 3
9 5003 2
结果：
用户ID 成功次数 失败次数 未知错误数
5001 2 1 1
5002 2 1 0
5003 0 1 1
请大家帮忙
------解决方案--------------------------------------------------------
select UserID as 用户ID,
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 表名
group by UserID
------解决方案--------------------------------------------------------
select userid as '用户ID '
, 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 test
group by userid
------解决方案--------------------------------------------------------
create proc myproc
as
select userid as '用户ID '
, 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 test
group by userid