Mysql获取按ID分组的下两个日期

Question

我有一个表，其中包含所有的eventID和事件日期。对于特定的eventID子集，我需要获取当前日期之后的下两个日期。我尝试了多次迭代，并查看了许多按组返回前N个结果的帖子-但我无法让这些示例中的任何一个正常工作，因为这些示例没有像我需要的那样使用日期。(例如：Using LIMIT within GROUP BY to get N results per group?)

+------------+-----+
| eDate      | ID  |
+------------+-----+
| 2021-10-27 | 1   |
+------------+-----+
| 2021-11-03 | 1   |
+------------+-----+
| 2021-11-10 | 1   |
+------------+-----+
| 2021-11-17 | 1   |
+------------+-----+
| 2021-11-24 | 1   |
+------------+-----+
| 2021-12-01 | 1   |
+------------+-----+
| 2021-12-08 | 1   |
+------------+-----+
| 2021-10-27 | 2   |
+------------+-----+
| 2021-11-03 | 2   |
+------------+-----+
| 2021-11-10 | 2   |
+------------+-----+
| 2021-11-17 | 2   |
+------------+-----+
| 2021-11-24 | 2   |
+------------+-----+
| 2021-12-01 | 2   |
+------------+-----+
| 2021-12-08 | 2   |
+------------+-----+
| 2021-10-27 | 3   |
+------------+-----+
| 2021-11-03 | 3   |
+------------+-----+
| 2021-11-10 | 3   |
+------------+-----+
| 2021-11-17 | 3   |
+------------+-----+
| 2021-11-24 | 3   |
+------------+-----+
| 2021-12-01 | 3   |
+------------+-----+
| 2021-12-08 | 3   |
+------------+-----+

SELECT eDate,ID,COUNT(*) as eCount FROM myTable WHERE ID in (1,3) AND eDate >=CURDATE() GROUP BY ID,eDate HAVING eCount < 3 ;

这个查询不起作用，因为GROUP BY ID,eDate创建了一个惟一的对，所以它返回ID 1和3的所有条目-都带有一个eCount = 1

从技术上讲，我实际上并不需要eCount，但它说明了我为获得正确结果所做的许多尝试之一。如果今天是2020-10-28，我想要的结果如下。

+------------+-----+--------+
| eDate      | ID  | eCount |
+------------+-----+--------+
| 2021-11-03 | 1   | 1      |
+------------+-----+--------+
| 2021-11-10 | 1   | 2      |
+------------+-----+--------+
| 2021-11-03 | 3   | 1      | 
+------------+-----+--------+
| 2021-11-10 | 3   | 2      |
+------------+-----+--------+

Answer 1

我想我找到了一个解决方案……我不知道它的伸缩性如何，也不知道我是否需要考虑其他注意事项，但我突然想到我不需要使用GROUP BY，因为我已经在寻找ID in (1,3)了。我所需要做的就是计算我正在查找的I的数量，并将其乘以2以设置为LIMIT

IE: 1,3 = LIMIT 4
SELECT * FROM myTable WHERE ID IN (1,3) AND eDate>=CURDATE() ORDER BY eDate LIMIT 4 ;

IE: 1,3,11, 15 = LIMIT 8
SELECT * FROM myTable WHERE ID IN (1,3,11,15) AND eDate>=CURDATE() ORDER BY eDate LIMIT 4 ;

这将抓取这些I的所有日期，按date...so排序前4或前8将是我需要的所有记录。

Answer 2

只有在当前日期之后有2个或更多日期时，您的解决方案才有效。观察此示例，如果其中一个id仅在当前日期之后出现1个日期，则此查询中的LIMIT 4：

SELECT * FROM myTable WHERE ID IN (1,3) AND eDate>=CURDATE() ORDER BY eDate LIMIT 4 ;

。。将用ID=1填充另一行。结果可能是这样的：

+------------+----+
|   eDate    | ID |
+------------+----+
| 2021-11-03 | 3  |
| 2021-11-03 | 1  |
| 2021-11-10 | 1  |
| 2021-11-17 | 1  |
+------------+----+

我猜那不是你想要的。

根据阿金娜的评论“在CTE中使用ROW_NUMBER()”，如果你使用的是支持窗口函数的MySQL版本，这可能是你需要的查询：

WITH cte AS (
SELECT eDate, ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY eDate) eCount
  FROM myTable WHERE eDate>=CURDATE()
  ORDER BY ID, eDate) 
SELECT * FROM cte 
WHERE eCount <=2;

您将获得出现在当前日期之后的所有ID的日期记录，可以灵活地定义特定的ID或不定义，并且不需要使用LIMIT。对于不支持窗口函数的旧MySQL版本，您可能可以仿效使用行号的相同想法，如下所示：

SELECT eDate, ID, rownum
FROM
   ( SELECT *,
           @rn:=CASE WHEN @i = ID AND eDate >= CURDATE()
                   THEN @rn + 1
                   ELSE 0 END AS rownum,
            @i := ID
        FROM myTable m  
         CROSS JOIN (SELECT @i:=0, @rn:=0)  r
       ORDER BY ID, eDate) v
 WHERE rownum > 0 AND rownum <= 2 
ORDER BY ID, eDate;

Demo fiddle

Answer 3

您没有指定eDate的类型，所以如果eDate类型是DATE (只有date而不是time)，我希望这能对您有所帮助。

SELECT eDate, ID, COUNT(ID) AS eCount
FROM myTable
WHERE ID IN (1,3) -- This condition return just two ids (1 and 3)
        AND eDate BETWEEN CONVERT(DATE, GETDATE()) AND CONVERT(DATE, DATEADD(DAY, 2, GETDATE())) -- This condition return the next two dates, beyond the current date ( current date = GETDATE() )
GROUP BY ID, eDate 
HAVING eCount < 3 -- This condition ensure select rows just have count loser than 3;

但是，如果eDate类型是DATETIME，则必须首先转换它。CONVERT(DATE, eDate)