SELECT
tor.id,
tor.pump_name,
tor.complete_date,
tor.head_user AS headUser,
tor.join_user as joinUser,
tor.ticket_id,
LEFT (tor.complete_date, 4),
CASE
WHEN substring(tor.complete_date, 6, 2) >= '07' THEN
'下半年'
ELSE
'上半年'
END
FROM
ticket_operate_record tor
LEFT JOIN ticket t ON t.ticket_id = tor.ticket_id
WHERE
t.ticket_type = '3'
AND complete_date IS NOT NULL
AND YEAR (tor.complete_date) = 2021
GROUP BY
tor.pump_name,
LEFT (complete_date, 4)
运行效果截图:
SELECT
tor.id,
tor.pump_name,
tor.complete_date,
tor.head_user AS headUser,
tor.join_user as joinUser,
tor.ticket_id,
LEFT (tor.complete_date, 4),
CASE
WHEN substring(tor.complete_date, 6, 2) >= '07' THEN
'下半年'
ELSE
'上半年'
END
FROM
ticket_operate_record tor
LEFT JOIN ticket t ON t.ticket_id = tor.ticket_id
WHERE
t.ticket_type = '3'
AND complete_date IS NOT NULL
AND YEAR (tor.complete_date) = 2021
GROUP BY
tor.pump_name,
LEFT (complete_date, 4),
CASE
WHEN substring(complete_date, 6, 2) >= '07' THEN
'下半年'
ELSE
'上半年'
END
运行效果截图:
可以看出来第二次运行比第一次多了一条结果,第一条sql分组不能区分上半年和下半年,第二条sql通过对
CASE
WHEN substring(complete_date, 6, 2) >= '07' THEN
'下半年'
ELSE
'上半年'
END
进行分组,实现了上半年和下半年的分组
根据如上,可以写出按照一年四季进行分组,分组sql如下:
CASE
WHEN substring(complete_date, 6, 2) >= '10' THEN
'第四季度'
WHEN substring(complete_date, 6, 2) >= '07' THEN
'第三季度'
WHEN substring(complete_date, 6, 2) >= '04' THEN
'第二季度'
ELSE
'第一季度'
END
如果有其他的分组需求,也可以通过 case when的方式自定义