//包括外层进行分组-也可以在里面进行分组
SELECT *,SUM(countSum) as countSum1 from (
SELECT
count_sum as countSum ,//数量
elt(INTERVAL (count_sum,0, 8, 16, 48 ),
'8','9-16','17-48','48+'
) AS countSumLevel,
FROM_UNIXTIME(createtime, '%Y-%m-%d'),
DATEDIFF(FROM_UNIXTIME(endtime, '%Y-%m-%d'),FROM_UNIXTIME(createtime, '%Y-%m-%d')) as days,//通过数量的0-8 9-16 17-48 48-更多 进行分组 第一组数据是进行分类的区间间隔,后面一组是别名
elt(
INTERVAL (
DATEDIFF(
FROM_UNIXTIME(endtime, '%Y-%m-%d'),
FROM_UNIXTIME(createtime, '%Y-%m-%d')
),0, 2, 4, 6,8,10,15 ),
'1-2',
'3-4',
'5-6',
'7-8',
'9-10',
'10-15',
'15+'
) AS daysLevel,//通过时间进行DATEDIFF 获取两个时间差的时间间隔 ,因为我的数据库存储的是时间戳,所以需要转换一个FROM_UNIXTIME 把时间戳转化为格式化时间
CONCAT_WS('/',elt(INTERVAL (count_sum,0, 8, 16, 48 ),
'8','9-16','17-48','48+') ,elt(
INTERVAL (
DATEDIFF(
FROM_UNIXTIME(endtime, '%Y-%m-%d'),
FROM_UNIXTIME(createtime, '%Y-%m-%d')
),0, 2, 4, 6,8,10,15 ),
'1-2','3-4','5-6','7-8','9-10','10-15','15+'
)) AS CountSumDay
//通过时间时间几个进行分组
FROM
tb_order
WHERE
endtime !=''
ORDER BY CountSum DESC
) as a
GROUP BY CountSumDay
我把结果贴出来