mysql - 选择每个关系的前 N ​​个项目，每个项目一行

Question

假设我有以下架构:

艺术家:

    +------------+------------------+------+-----+---------+----------------+
    | Field      | Type             | Null | Key | Default | Extra                  |
    +------------+------------------+------+-----+---------+----------------+
    | id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |                   |
    | name       | varchar(255)     | YES  | UNI | NULL    |                |
    +------------+------------------+------+-----+---------+----------------+

事件:

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra              |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment     |
| date       | timestamp        | YES  |     | NULL    |                |
| artist_id  | int(11)          | YES  |     | NULL    |                |
| venue_id   | int(11)          | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

Assets :

+---------------------+------------------+------+-----+---------+----------------+
| Field               | Type             | Null | Key | Default | Extra          |
+---------------------+------------------+------+-----+---------+----------------+
| id                  | int(10) unsigned | NO   | PRI | NULL    | a
| event_id            | int(11)          | YES  |     | NULL    |                 
| source_asset_title  | varchar(255)     | YES  |     | NULL    |                |
| source_created_time | timestamp        | YES  |     | NULL    |                |

对于给定 artist.id 的每个事件，我想要一个包含 4 个 Assets 的结果集，按事件日期排序，例如:

+----------+----------+------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+
| event_id | asset_id | source_asset_title                                                                                                     | event_date          | date                |
+----------+----------+------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+
|        1 |     2089 | aba                                                                                                                       | 2015-12-03 07:00:00 | 2015-12-03 07:00:00 |
|        1 |     2101 | abb                                                                                                                        | 2015-12-03 07:00:00  | 2011-04-07 15:30:00 |
|        1 |     2102 | abc                                                                                                                    | 2015-12-03 07:00:00  | 2011-05-22 16:00:00 |
|        1 |     2107 | abd                                                                                                                     | 2015-12-03 07:00:00  | 2011-06-11 15:00:00 |
|        2 |     2109 | abe                                                                                                                     | 2011-07-18 15:00:00  | 2011-07-18 15:00:00 |
|        2 |     2113 | abf                                                                                                                    | 2011-07-18 15:00:00  | 2011-07-24 15:30:00 |
|        2 |     2115 | abg                                                                                                                    | 2011-07-18 15:00:00  | 2011-08-25 16:00:00 |
|        2 |     2123 | abh                                                                                                                    | 2011-07-18 15:00:00  | 2011-08-28 16:00:00 |
|        3 |     2126 | abi                                                                                                                    | 2011-09-01 16:00:00  | 2011-09-01 16:00:00 |
|        3 |     2129 | abj                                                                                                                    | 2011-09-01 16:00:00  | 2011-09-10 16:00:00 |
|        3 |     2135 | abk                                                                                                                    | 2011-09-01 16:00:00  | 2011-10-14 16:00:00 |
|        3 |     2147 | abl                                                                                                                    | 2011-09-01 16:00:00  | 2011-10-22 16:00:00 |

如果每个事件没有一个子查询，我如何实现这一目标？

我相信这里的模式和结果集使这个问题与 StackExchange 上的其他问题足够不同，因此一个新问题是合适的。

Answer 1

这是 artist.id=1 的 SQL，按 events.date 从新到旧排序

SELECT
  events.id as event_id,
  assets.id as asset_id,
  source_asset_title
FROM assets
INNER JOIN events ON events.id = assets.event_id
INNER JOIN artists ON artists.id = events.artist_id
WHERE artists.id = 1
ORDER BY events.date DESC