Mysql查询 - 每个日期最多3个数据

these is my DB table data,{ i have maintained only one Table}

这些是我的数据库表数据,{我只保留了一个表}

I need to fetch max 3 data from each start_date,

我需要从每个start_date获取最多3个数据,

give me any idea to develop query,,

给我任何开发查询的想法,

2 个解决方案

#1

SELECT a.*
FROM Table1 a
INNER JOIN Table1 b ON a.start_date = b.start_date AND a.event_id <= b.event_id
GROUP BY a.event_id,a.start_date
HAVING COUNT(*) <= 3
ORDER BY a.start_date

#2

I may suggest you this query -

我可能会建议你这个查询 -

SELECT * FROM (
  SELECT t1.*, COUNT(*) pos FROM table t1
    LEFT JOIN table t2
      ON t2.start_date = t1.start_date AND t2.event_id <= t1.event_id
  GROUP BY
    t1.start_date AND t1.event_id
  ) t
WHERE
  pos <= 3;

It selects 3 rows with min event_id in a start_date group.

它在start_date组中选择带有min event_id的3行。

#1

SELECT a.*
FROM Table1 a
INNER JOIN Table1 b ON a.start_date = b.start_date AND a.event_id <= b.event_id
GROUP BY a.event_id,a.start_date
HAVING COUNT(*) <= 3
ORDER BY a.start_date

#2