现有表tb_resume:结构为
resumeid(主键id) userid(外键int) tag(int) lizhi(int) kaichu(int) quantui(int) youxiu(int) 等字段
每个字段值为1和0现在要以一条查询语句统计出:以userid分组,tag=1的count,lizhi=1的count,kaichu=1的count,quantui=1的count,youxiu=1的count怎么写这条SQL语句呀,高手请帮帮忙,谢谢~~~小弟在线等
resumeid(主键id) userid(外键int) tag(int) lizhi(int) kaichu(int) quantui(int) youxiu(int) 等字段
每个字段值为1和0现在要以一条查询语句统计出:以userid分组,tag=1的count,lizhi=1的count,kaichu=1的count,quantui=1的count,youxiu=1的count怎么写这条SQL语句呀,高手请帮帮忙,谢谢~~~小弟在线等
sum(case when tag=1 then 1 else 0 end) [tag=1],
sum(case when lizhi=1 then 1 else 0 end) [lizhi=1],
sum(case when kaichu=1 then 1 else 0 end) [kaichu=1],
sum(case when youxiu=1 then 1 else 0 end) [youxiu=1]
from tb_resume
group by userid
select userid ,
sum(case when tag=1 then 1 else 0 end) [tag=1],
sum(case when lizhi=1 then 1 else 0 end) [lizhi=1],
sum(case when kaichu=1 then 1 else 0 end) [kaichu=1],
sum(case when youxiu=1 then 1 else 0 end) [youxiu=1]
from tb_resume
group by userid--方法二
select userid ,
isnull((select count(1) from tb where userid = t.userid and tag=1),0) [tag=1],
isnull((select count(1) from tb where userid = t.userid and lizhi=1),0) [lizhi=1],
isnull((select count(1) from tb where userid = t.userid and kaichu=1),0) [kaichu=1],
isnull((select count(1) from tb where userid = t.userid and youxiu=1),0) [youxiu=1]
from tb_resume t
group by userid
select userid,
[tag数]=sum(case tag when 1 then 1 else 0 end),
[lizhi数]=sum(case lizhi when 1 then 1 else 0 end),
[kaichu数]=sum(case kaichu when 1 then 1 else 0 end),
[quantui数]=sum(case quantui when 1 then 1 else 0 end),
[youxiu数]=sum(case youxiu when 1 then 1 else 0 end)
from tb_resume group by userid