select
count(*) as 人数,case when staycount=1 then '新客户'
when staycount>=2 and staycount<10 then '2-10次'
when staycount>=10 and staycount<20 then '10-20次'
when staycount>=21 and staycount<50 then '21-50次'
when staycount>=51 and staycount<100 then '51-100次'
when staycount>=100 then '100次以上'
end as 次数
from Device group by
case when staycount=1 then '新客户'
when staycount>=2 and staycount<10 then '2-10次'
when staycount>=10 and staycount<20 then '10-20次'
when staycount>=21 and staycount<50 then '21-50次'
when staycount>=51 and staycount<100 then '51-100次'
when staycount>=100 then '100次以上'
end
order by 次数
结果如下
2 100次以上
6 10-20次
12 2-10次
1 21-50次
4 51-100次
2 新客户
如何按
次数多少，也就是staycount的多少排序啊。都是中文的，我无语了。
------解决方案--------------------
SELECT *
FROM ( SELECT COUNT(*) AS 人数 ,
CASE WHEN staycount = 1 THEN '新客户'
WHEN staycount >= 2
AND staycount < 10 THEN '2-10次'
WHEN staycount >= 10
AND staycount < 20 THEN '10-20次'
WHEN staycount >= 21
AND staycount < 50 THEN '21-50次'
WHEN staycount >= 51
AND staycount < 100 THEN '51-100次'
WHEN staycount >= 100 THEN '100次以上'
END AS 次数
FROM Device
GROUP BY CASE WHEN staycount = 1 THEN '新客户'
WHEN staycount >= 2
AND staycount < 10 THEN '2-10次'
WHEN staycount >= 10
AND staycount < 20 THEN '10-20次'
WHEN staycount >= 21
AND staycount < 50 THEN '21-50次'
WHEN staycount >= 51
AND staycount < 100 THEN '51-100次'
WHEN staycount >= 100 THEN '100次以上'