mysql 时间排序 前3天_MySQL 取前三十天每天的日期 组内排序 拓展

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)