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)

在这里插入图片描述

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值