时间戳

首页 > 解决方案 > 如何在 SQL 中 JOIN 后获取最后 N 条记录？

mysql - 如何在 SQL 中 JOIN 后获取最后 N 条记录？

问题描述

我在 mysql 中有三个表 -
表 1：注释

+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | int         | NO   | PRI | NULL    | auto_increment |
| user_id       | int         | NO   | MUL | NULL    |                |
| note_id       | varchar(25) | NO   | UNI | NULL    |                |
| topic         | text        | NO   |     | NULL    |                |
| note_html     | text        | NO   |     | NULL    |                |
| note_markdown | text        | NO   |     | NULL    |                |
| access_type   | varchar(25) | NO   |     | NULL    |                |
| date          | date        | NO   |     | NULL    |                |
| time          | time        | NO   |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+

表 2：notes_subject

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int         | NO   | PRI | NULL    | auto_increment |
| user_id      | int         | NO   |     | NULL    |                |
| note_id      | varchar(25) | NO   | MUL | NULL    |                |
| subject_name | text        | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

表 3：notes_tag

+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| user_id  | int         | NO   |     | NULL    |                |
| note_id  | varchar(25) | NO   | MUL | NULL    |                |
| tag_name | text        | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

并假设存储在notes表中的内容为（我将保留此表的一些属性，例如note_html，note_markdown，因为它具有大量数据）-

+----+---------+--------------+------------+----------+
| id | user_id | note_id      | date       | time     |
+----+---------+--------------+------------+----------+
|  1 |       2 | nVf0It70bnjQ | 2020-08-08 | 11:18:17 |
|  3 |       2 | RfYg3u59Ytup | 2020-08-06 | 12:50:51 |
|  6 |       1 | e657ZxsYaWR2 | 2020-08-06 | 10:43:08 |
|  7 |       2 | ARJuTcItPgbn | 2020-08-10 | 11:19:11 |
|  8 |       2 | BW2aMLYN8CIF | 2020-08-10 | 11:24:35 |
+----+---------+--------------+------------+----------+

并假设存储在notes_subject表中的内容为 -

+----+---------+--------------+--------------+
| id | user_id | note_id      | subject_name |
+----+---------+--------------+--------------+
|  1 |       2 | nVf0It70bnjQ | computer     |
|  3 |       2 | RfYg3u59Ytup | SQL          |
|  6 |       1 | e657ZxsYaWR2 | computer     |
|  7 |       2 | ARJuTcItPgbn | CN           |
|  8 |       2 | BW2aMLYN8CIF | DLD          |
+----+---------+--------------+--------------+

并假设存储在notes_tag表中的内容为 -

+-----+---------+--------------+--------------+
| id  | user_id | note_id      | tag_name     |
+-----+---------+--------------+--------------+
|  7  |       1 | e657ZxsYaWR2 |  definition  |
|  70 |       2 | nVf0It70bnjQ | computer fan |
|  71 |       2 | nVf0It70bnjQ |  clean       |
| 250 |       2 | RfYg3u59Ytup | sql          |
| 251 |       2 | RfYg3u59Ytup | mysql        |
| 252 |       2 | RfYg3u59Ytup |  select      |
| 253 |       2 | RfYg3u59Ytup |  ASC         |
| 254 |       2 | RfYg3u59Ytup |  DESC        |
| 257 |       2 | ARJuTcItPgbn | cn           |
| 258 |       2 | ARJuTcItPgbn |  IPV4        |
| 261 |       2 | BW2aMLYN8CIF | AND          |
| 262 |       2 | BW2aMLYN8CIF |  NAND        |
+-----+---------+--------------+--------------+

我用JOIN这三个表 -

SELECT N.id, N.user_id, N.note_id, S.subject_name, T.tag_name, N.date, N.time 
from notes AS N 
JOIN notes_subject AS S ON N.user_id = 2 
  AND N.user_id = S.user_id 
  AND N.note_id = S.note_id 
JOIN notes_tag AS T ON N.user_id = T.user_id 
  AND N.note_id = T.note_id 
ORDER BY N.date DESC
LIMIT 3

它获取了 -
结果 1：

+----+---------+--------------+--------------+----------+------------+----------+
| id | user_id | note_id      | subject_name | tag_name | date       | time     |
+----+---------+--------------+--------------+----------+------------+----------+
|  7 |       2 | ARJuTcItPgbn | CN           | cn       | 2020-08-10 | 11:19:11 |
|  7 |       2 | ARJuTcItPgbn | CN           |  IPV4    | 2020-08-10 | 11:19:11 |
|  8 |       2 | BW2aMLYN8CIF | DLD          | AND      | 2020-08-10 | 11:24:35 |
+----+---------+--------------+--------------+----------+------------+----------+

如果我在没有 的情况下运行上面的查询LIMIT，它会获取 -
结果 2：

+----+---------+--------------+--------------+--------------+------------+----------+
| id | user_id | note_id      | subject_name | tag_name     | date       | time     |
+----+---------+--------------+--------------+--------------+------------+----------+
|  7 |       2 | ARJuTcItPgbn | CN           | cn           | 2020-08-10 | 11:19:11 |
|  7 |       2 | ARJuTcItPgbn | CN           |  IPV4        | 2020-08-10 | 11:19:11 |
|  8 |       2 | BW2aMLYN8CIF | DLD          | AND          | 2020-08-10 | 11:24:35 |
|  8 |       2 | BW2aMLYN8CIF | DLD          |  NAND        | 2020-08-10 | 11:24:35 |
|  1 |       2 | nVf0It70bnjQ | computer     | computer fan | 2020-08-08 | 11:18:17 |
|  1 |       2 | nVf0It70bnjQ | computer     |  clean       | 2020-08-08 | 11:18:17 |
|  3 |       2 | RfYg3u59Ytup | SQL          | sql          | 2020-08-06 | 12:50:51 |
|  3 |       2 | RfYg3u59Ytup | SQL          | mysql        | 2020-08-06 | 12:50:51 |
|  3 |       2 | RfYg3u59Ytup | SQL          |  select      | 2020-08-06 | 12:50:51 |
|  3 |       2 | RfYg3u59Ytup | SQL          |  ASC         | 2020-08-06 | 12:50:51 |
|  3 |       2 | RfYg3u59Ytup | SQL          |  DESC        | 2020-08-06 | 12:50:51 |
+----+---------+--------------+--------------+--------------+------------+----------+

在结果 1中：最后插入了三行 ID (7,7,8)。但结果必须像 -

+----+---------+--------------+--------------+--------------+------------+----------+
| id | user_id | note_id      | subject_name | tag_name     | date       | time     |
+----+---------+--------------+--------------+--------------+------------+----------+
|  7 |       2 | ARJuTcItPgbn | CN           | cn           | 2020-08-10 | 11:19:11 |
|  7 |       2 | ARJuTcItPgbn | CN           |  IPV4        | 2020-08-10 | 11:19:11 |
|  8 |       2 | BW2aMLYN8CIF | DLD          | AND          | 2020-08-10 | 11:24:35 |
|  8 |       2 | BW2aMLYN8CIF | DLD          |  NAND        | 2020-08-10 | 11:24:35 |
|  1 |       2 | nVf0It70bnjQ | computer     | computer fan | 2020-08-08 | 11:18:17 |
|  1 |       2 | nVf0It70bnjQ | computer     |  clean       | 2020-08-08 | 11:18:17 |
+----+---------+--------------+--------------+--------------+------------+----------+

结果必须包含在特定 ID 的所有 tag_name 中。

标签： mysqlsqljoin

解决方案

将限制放在子查询中，该子查询在加入前获取 3 个最新注释。

SELECT N.id, N.user_id, N.note_id, S.subject_name, T.tag_name, N.date, N.time 
from (SELECT *
      FROM notes
      WHERE user_id = 2
      ORDER BY date DESC
      LIMIT 3) AS N 
JOIN notes_subject AS S ON N.user_id = S.user_id 
                       AND N.note_id = S.note_id 
JOIN notes_tag AS T ON N.user_id = T.user_id 
                   AND N.note_id = T.note_id

推荐阅读