mysql 的case when 和group by同时使用和按某字段固定顺序排序

select sum(a.num),a.period from
(select sum(visiting_num) num,
(case when (SUBSTRING(visiting_date,9,2)+0 <10) then '上旬'
when (SUBSTRING(visiting_date,9,2)+0 >= 20) then '下旬'
else '中旬' end)period
from t_vistor_register
where is_delete='0'
and SUBSTRING(visiting_date,1,7)=DATE_FORMAT(now(),'%Y-%m')
group by visiting_date)a
group by a.period
order by a.period

通过前嵌套查询可实现，如果写作

group by (case when (SUBSTRING(visiting_date,9,2)+0 <10) then '上旬' when (SUBSTRING(visiting_date,9,2)+0 >= 20) then '下旬' else '中旬' end)

在sql_mode=ONLY_FULL_GROUP_BY 时，亲测会报错

不止是case when ，对字段做过其他处理的道理相同，如：

select sum(a.num),a.period from
(select sum(visiting_num) num,
       date_format(str_to_date(visiting_date, '%Y-%m-%d'),'%W') period
from t_vistor_register where is_delete='0'
     and YEARWEEK(date_format(str_to_date(visiting_date, '%Y-%m-%d'),'%Y-%m-%d'),1)= YEARWEEK(now(),1)
     group by visiting_date)a
group by a.period
order by FIND_IN_SET(a.period,'Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday')

其中 order by FIND_IN_SET(a.period,'Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday') 便实现了按固定顺序排序，需要注意的是 字段 a.period不需要加引号（亲测），网上很多教程都加了引号会起不到排序的效果