php 近一周数据,php – MYSQL获取每周数据的有限数据

我正在向用户显示当前周事件..从当前日期获取数据到当前日期值的7天..它已经实现了

通过使用这些查询

SELECT * from events WHERE ( start_date BETWEEN '2013-01-01' AND '2013-01-07' ) order by start_date ASC

我有问题,如果,

start_date 2013-01-01 have 10 records

start_date 2013-01-02 have 1 records

start_date 2013-01-03 have 12 records

....

...

然后从DB获取数据需要更多时间..

所以,我每天只需要显示3个数据

我的表格,

id start_date end_date event_title

1 2013-01-01 2013-01-03 event_1

2 2013-01-01 2013-01-01 event_2

3 2013-01-01 2013-01-01 event_3

4 2013-01-01 2013-01-01 event_4

- 2013-01-01 2013-01-01 event_4

- 2013-01-01 2013-01-01 event_4

5 2013-01-02 2013-01-02 event_5

6 2013-01-03 2013-01-03 event_6

7 2013-01-03 2013-01-03 event_7

8 2013-01-03 2013-01-03 event_8

9 2013-01-03 2013-01-03 event_9

10 2013-01-04 2013-01-04 event_10

预期产量为,

id start_date end_date event_title

1 2013-01-01 2013-01-03 event_1

2 2013-01-01 2013-01-01 event_2

3 2013-01-01 2013-01-01 event_3

4 2013-01-02 2013-01-02 event_5

5 2013-01-03 2013-01-03 event_6

6 2013-01-03 2013-01-03 event_7

7 2013-01-03 2013-01-03 event_8

8 2013-01-04 2013-01-04 event_10

任何人都可以帮助解决这些……

可以在单个查询中

解决方法:

此查询应该有效

SET @level = 0;

SET @group = '';

SELECT

*

FROM (

SELECT

id,

start_date,

end_date,

event_title,

@level := IF(@group = start_date, @level+1, 1) AS LEVEL,

@group := start_date AS StartDate

FROM test

/*WHERE start_date BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAY)*/

ORDER BY start_date

) rs

WHERE LEVEL < 4

这将在每个日期获取3条记录.如果您想根据需要在条件取消注释的情况下申请

Demo

标签：php,mysql,date,select

来源： https://codeday.me/bug/20190517/1123419.html