mysql - 如何通过一个表的id从两个表分组获取数据

Question

我需要解决这个问题:

我有一个主题表:

+-------------+--------------+------+-----+---------------------+----------------+
| Field       | Type         | Null | Key | Default             | Extra          |
+-------------+--------------+------+-----+---------------------+----------------+
| id          | int(11)      | NO   | PRI | NULL                | auto_increment |
| main_title  | varchar(30)  | NO   |     | ""                  |                |
| sub_title   | varchar(30)  | NO   |     | ""                  |                |
| type        | tinyint(4)   | NO   |     | 1                   |                |
| thumbnail   | int(11)      | NO   |     | 0                   |                |
| main_pic    | int(11)      | NO   |     | 0                   |                |
| info_url    | varchar(128) | NO   |     | ""                  |                |
| create_time | timestamp    | NO   |     | CURRENT_TIMESTAMP   |                |
| modify_time | timestamp    | NO   |     | 0000-00-00 00:00:00 |                |
| status      | tinyint(4)   | NO   |     | 1                   |                |
+-------------+--------------+------+-----+---------------------+----------------+

一个新表:

+---------------+---------------+------+-----+---------------------+----------------+
| Field         | Type          | Null | Key | Default             | Extra          |
+---------------+---------------+------+-----+---------------------+----------------+
| id            | int(11)       | NO   | PRI | NULL                | auto_increment |
| title         | varchar(60)   | NO   |     |                     |                |
| thumbnail     | int(11)       | NO   |     | 0                   |                |
| abstract      | varchar(2048) | NO   |     |                     |                |
| paragraphs    | text          | NO   |     | NULL                |                |
| about_subject | varchar(128)  | NO   |     |                     |                |
| pv            | int(11)       | NO   |     | 0                   |                |
| share_count   | int(11)       | NO   |     | 0                   |                |
| like_count    | int(11)       | NO   |     | 0                   |                |
| comment_count | int(11)       | NO   |     | 0                   |                |
| source        | varchar(64)   | NO   |     |                     |                |
| source_url    | varchar(128)  | NO   | UNI |                     |                |
| signature     | varchar(40)   | NO   |     |                     |                |
| creator_id    | int(11)       | NO   |     | 0                   |                |
| create_time   | timestamp     | NO   |     | CURRENT_TIMESTAMP   |                |
| publish_time  | timestamp     | NO   |     | 0000-00-00 00:00:00 |                |
| editor_id     | int(11)       | NO   |     | 0                   |                |
| modify_time   | timestamp     | NO   |     | 0000-00-00 00:00:00 |                |
| status        | tinyint(4)    | NO   | MUL | 0                   |                |
+---------------+---------------+------+-----+---------------------+----------------+

现在，我想在一次查询中获取每个主题的前 20 条最新消息。我试过:

SELECT 
concat('3','_',news.id) as id, 3 as target_type, 
news.id as target_id, news.title as target_title, 
news.abstract as target_abstract, news.paragraphs as target_paragraphs, 
news.source as target_source, news.pv as target_pv, 
news.like_count as target_like_count, news.comment_count as target_comment_count, news.publish_time as target_create_time,
subject.id as subject_id, subject.main_title as subject_name 
  FROM
  subject
  LEFT JOIN news ON subject.id = news.about_subject 
  ORDER BY news.publish_time DESC
  -- LIMIT 0 ,20

如何获取每个主题的前 20 名并按 subject.id 对所有数据进行排序？

Answer 1

我会考虑选择一个子查询，它将是您的两个表或这些表的 View 的联合。

This can be done in MySQL, but it is not as simple as adding a LIMIT clause. Here is an article that explains the problem in detail:

How to select the first/least/max row per group in SQL

It's a good article - he introduces an elegant but naïve solution to the "Top N per group" problem, and then gradually improves on it.

--丹本

完整问题和文章答案: https://stackoverflow.com/a/2129703/3536236