一句sql 分组统计数量

[size=x-large]select
count(TsUser.FId),

(case when TsUser.FStoreId is not null then (select s.FName from TuStore s where s.FId=TsUser.FStoreId ) end ) a, //用户的门店ID不未空 就显示当前门店名称

(case TcWxProfile.FIsSubscribe when 0 then '未关注' when 1 then '已关注' else '未关注' end ) sub,

count(case when TcWxProfile.FIsSubscribe=1 then TcWxProfile.FIsSubscribe end) s, //统计已关注

count(case when TcWxProfile.FIsSubscribe is null then TcWxProfile.FId when TcWxProfile.FIsSubscribe=0 then TcWxProfile.FId end) //统计取消关注或未关注

from TcWxProfile
inner join TsUser on TcWxProfile.FUserId=TsUser.FId
inner join TcCustomer on TsUser.FTypeInstanceId=TcCustomer.FId

group by a

order by count(TsUser.FId) DESC[/size]


case 字段名 when 值 then 结果

CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END salary_class,

date_format(字段名,'%y-%m-%d') 显示2015-10-10