查最近10个有数据的日期

先来讲一下需求:本文要查的是最近有数据的10天的记录,假如数据库中的字段create_time= 2016/9/26有三条数据,create_time= 2016/9/24有两条数据,那么26号,24号就是前两天有数据的记录,以此类别,查询最近有数据的10天的记录。

先根据日期分组

SELECT created_at FROM tableName group by DATE_FORMAT(created_at,'%m-%d-%Y')

根据日期从大到小排序,找到最小的日期

(SELECT created_at FROM `business` group by DATE_FORMAT(created_at,'%m-%d-%Y') order by created_at desc limit 9,1)

You can use LIMIT 2,1 instead of WHERE row_number() = 3.

As the documentation explains, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return.

Keep in mind that it’s an 0-based index. So, if you want the line number n, the first argument should be n-1. The second argument will always be 1, because you just want one row. For example, if you want the line number 56 of a table customer:

SELECT * FROM customer LIMIT 55,1

将刚刚查询到的日期作为下次查询的条件

Select *  FROM `business` where created_at >=   (SELECT created_at FROM `business` group by DATE_FORMAT(created_at,'%m-%d-%Y') order by created_at desc limit 9,1) order by created_at desc
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值