mysql 取前三十天日期:
SELECT @rownum:=@rownum+1 AS rownum,DATE_SUB(SYSDATE(),INTERVAL @rownum DAY) as da FROM (SELECT @rownum:=0) r,tableName limit 0,30;
设置一个变量,获取行号,然后获取变量内的数据用于 DATE_SUB 函数来获取前 rownum 数量的天
组内排序:
SELECT
tb.rownum AS rownum,
tb.uid AS uid,
tb.user_id AS user_id,
tb.event_code AS event_code,
tb.points AS points,
tb.operation AS operation,
create_time
FROM
(
SELECT
( @i := CASE WHEN @complete_user_id = complete_user_id THEN @i + 1 ELSE 1 END ) rownum,
min( id ) AS uid,
complete_user_id AS user_id,
'ZeefDnXIeqKJfiBe' AS event_code,
5 AS points,
'确认请假' AS operation,
create_time,
( @complete_user_id := complete_user_id )
FROM
health_statis.t_ems_vacate_complete,
( SELECT @i := 0, @complete_user_id := '' ) AS a
GROUP BY
date_format( create_time, '%Y%m%d' ),
complete_user_id
ORDER BY
rownum
) tb
WHERE
create_time >= ' 2019-09-26 16:53:54'
AND rownum <= 4
LIMIT 10;
COALESCE 函数,把值为 null 的结果转换为 0
CONALESCE(null,0)