mysql - 以更简单的方式获取每个组的最新元素(most recent elements)

Question

我有一个日程表，我可以在其中使用 recurring_id 数据将某些事件分组为同一事件。问题是我只需要在按 recurring_id 分组的事件列表中获取一个事件(最近的一个)，但如果 recurring_id 为 0，我只需要列表，顶部 5 个元素。事件表:

+----+---------------------+---------------+
| id |        start        | recurrence_id |
+----+---------------------+---------------+
|  1 | 2013-10-03 03:30:00 |             0 |
|  2 | 2013-10-04 03:30:00 |             0 |
|  3 | 2013-10-05 03:30:00 |             1 |
|  4 | 2013-10-12 03:30:00 |             1 |
|  5 | 2013-10-19 03:30:00 |             1 |
|  6 | 2013-10-26 03:30:00 |             1 |
|  7 | 2013-10-13 03:30:00 |             2 |
|  8 | 2013-10-06 03:30:00 |             2 |
+----+---------------------+---------------+

我有这个查询(假设当前日期是 2013-10-03T21:18:10+00:00)

SELECT * FROM
((
    SELECT * FROM events e JOIN
    (
        SELECT recurrence_id, MIN(start) start FROM events 
        WHERE recurrence_id <> 0
        AND start > '2013-10-03T21:18:10+00:00' 
        GROUP BY recurrence_id
    ) subq USING (recurrence_id, start)
    ORDER BY start ASC
    LIMIT 5
) UNION (
    SELECT * FROM events e 
    WHERE start > '2013-10-03T21:18:10+00:00' 
    AND recurrence_id = 0
    ORDER BY start ASC
    LIMIT 5
)) sq
ORDER BY start ASC
LIMIT 5

它完成了工作，但我认为我过于复杂了，而且创建有效的索引变得非常困难 :S

预期输出为:

+----+---------------------+---------------+
| id |        start        | recurrence_id |
+----+---------------------+---------------+
|  2 | 2013-10-04 03:30:00 |             0 |
|  3 | 2013-10-05 03:30:00 |             1 |
|  8 | 2013-10-06 03:30:00 |             2 |
+----+---------------------+---------------+

Answer 1

获取数据的最有效方法是使用几个用户定义的变量进行排名:

SELECT ID, recurrence_id, start
FROM ( 
  SELECT 
    ID, recurrence_id, start,
    @rownum := IF(@prev = recurrence_id, @rownum + 1, 1) rank,
    @prev := recurrence_id 
  FROM events, (SELECT @rownum := NULL, @prev := NULL) init
  WHERE start > '2013-10-03T21:18:10+00:00'
  ORDER BY recurrence_id, start
) s 
WHERE (recurrence_id = 0 AND rank <= 5) OR (recurrence_id != 0 AND rank = 1)
ORDER BY recurrence_id, start

根据您的示例数据，这将输出:

| ID | RECURRENCE_ID |                     START |
|----|---------------|---------------------------|
|  2 |             0 | October, 04 2013 03:30:00 |
|  3 |             1 | October, 05 2013 03:30:00 |
|  8 |             2 | October, 06 2013 03:30:00 |