在SQL中进行JOIN后获取最后N条记录吗? [英] Get last N records after JOIN in SQL?
问题描述
我正在为自己创建一个网站，可以在任何主题上创建笔记.
要创建笔记，我必须提供主题名称，主题，标签，笔记内容.
每当我按下保存"按钮以保存我的新笔记时，它将所有信息存储在给定的以下三个表中的MYSQL DB中.每个注释还有一个唯一的note_id.
我可以为单个笔记提供多个标签，这样一个标签将存储在一行中，第二个标签将存储在另一行中，依此类推..在notes_tag table中.
I am creating a website for myself where I can create notes on any subjects.
For creating a note I have to give subject name, topic, tags, note content.
Whenever I press save button to save my new note it will store all the information in MYSQL DB in given bellow three tables. There is also a unique note_id for each note.
I can give multiple tag for a single note so one tag will store in a single row, 2nd tag will store in another row and so on.. in notes_tag table.
MYSQL DB中有三个表-
There are three tables in MYSQL DB -
- 笔记
- notes_subject
- notes_tag
因此，如果我想按日期和时间降序(STACK ORDER)来获取 user_id = 2 的所有注释，我将在下面的查询中运行-
So If I want to fetch all notes of the user_id =2 with date and time in descending order (STACK ORDER), I run given below query -
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, time DESC;
并获取-
+----+---------+--------------+--------------+--------------+------------+----------+
| id | user_id | note_id | subject_name | tag_name | date | time |
+----+---------+--------------+--------------+--------------+------------+----------+
| 28 | 2 | DFbw8bOhVvuY | Logo | logo | 2020-08-11 | 12:21:09 |
| 8 | 2 | BW2aMLYN8CIF | DLD | AND | 2020-08-10 | 11:24:35 |
| 8 | 2 | BW2aMLYN8CIF | DLD | NAND | 2020-08-10 | 11:24:35 |
| 7 | 2 | ARJuTcItPgbn | CN | cn | 2020-08-10 | 11:19:11 |
| 7 | 2 | ARJuTcItPgbn | CN | IPV4 | 2020-08-10 | 11:19:11 |
| 25 | 2 | mpngBCarbHuu | new norw | kjk45 | 2020-08-10 | 06:13:48 |
| 24 | 2 | 7zdqslgj2AFd | subject1 | tag1 | 2020-08-10 | 06:12:37 |
| 23 | 2 | LaHLcnPotDat | subject2 | l;k | 2020-08-10 | 06:10:20 |
| 22 | 2 | FgGqsVFrdjSH | subject3 | JGH | 2020-08-10 | 06:09:02 |
| 21 | 2 | rrsz6eIh1sny | K | lkj | 2020-08-10 | 06:07:56 |
| 20 | 2 | NNLhTXSqZcbs | subject5 | lkj | 2020-08-10 | 06:02:47 |
| 19 | 2 | nOYQunMfi09p | subject16 | j | 2020-08-10 | 06:02:28 |
| 18 | 2 | GYI2B8A5XWBP | subject13 | lk | 2020-08-10 | 06:01:58 |
| 16 | 2 | 3mFvXIt5KVx8 | subject11 | kkklk | 2020-08-10 | 06:00:18 |
| 15 | 2 | jisgFUdh1jAu | subject15 | ;lksd | 2020-08-10 | 05:52:41 |
| 14 | 2 | Hnmv4AXvgn0m | subject10 | lksd | 2020-08-10 | 05:51:50 |
| 13 | 2 | JgsebjXpR7w3 | subject9 | lkals | 2020-08-10 | 05:50:53 |
| 12 | 2 | EvCAPWsntTIO | subject8 | lklas | 2020-08-10 | 05:48:12 |
| 11 | 2 | 86ZTfX641bCA | subject7 | alsk | 2020-08-10 | 05:47:14 |
| 10 | 2 | ZqlvwNvzA7fn | subject6 | lkjas | 2020-08-10 | 05:44:50 |
| 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 | DESC | 2020-08-06 | 12:50:51 |
| 3 | 2 | RfYg3u59Ytup | SQL | ASC | 2020-08-06 | 12:50:51 |
| 3 | 2 | RfYg3u59Ytup | SQL | select | 2020-08-06 | 12:50:51 |
| 3 | 2 | RfYg3u59Ytup | SQL | mysql | 2020-08-06 | 12:50:51 |
| 3 | 2 | RfYg3u59Ytup | SQL | sql | 2020-08-06 | 12:50:51 |
+----+---------+--------------+--------------+--------------+------------+----------+
27 rows in set (0.01 sec)
注意::还有两个属性'note_html'，'note_markdown'，其中包含我的注释内容.由于数据量大，我没有在SQL查询的结果中同时包含这两个属性.
但是，如果我要最后3 或最后N 插入user_id = 2的注释.查询应该是什么?
Note : There are two more attributes 'note_html', 'note_markdown' which contains my note content. I haven't include both attribute in result of SQL query due to its large data.
But If I want last 3 or last N inserted notes of the user_id=2 . What should be the query ?
最后3个插入的user_id = 2注释的结果必须类似于-
The result of last 3 inserted notes of user_id=2 must be like -
+----+---------+--------------+--------------+--------------+------------+----------+
| id | user_id | note_id | subject_name | tag_name | date | time |
+----+---------+--------------+--------------+--------------+------------+----------+
| 28 | 2 | DFbw8bOhVvuY | Logo | logo | 2020-08-11 | 12:21:09 |
| 8 | 2 | BW2aMLYN8CIF | DLD | AND | 2020-08-10 | 11:24:35 |
| 8 | 2 | BW2aMLYN8CIF | DLD | NAND | 2020-08-10 | 11:24:35 |
| 7 | 2 | ARJuTcItPgbn | CN | cn | 2020-08-10 | 11:19:11 |
| 7 | 2 | ARJuTcItPgbn | CN | IPV4 | 2020-08-10 | 11:19:11 |
+----+---------+--------------+--------------+--------------+------------+----------+
或者如果我要插入前2个user_id = 2的注释，结果必须为-
OR If I want first 2 inserted notes of user_id=2, result must be -
+----+---------+--------------+--------------+--------------+------------+----------+
| 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 | 2 | nVf0It70bnjQ | computer | clean | 2020-08-08 | 11:18:17 |
| 1 | 2 | nVf0It70bnjQ | computer | computer fan | 2020-08-08 | 11:18:17 |
+----+---------+--------------+--------------+--------------+------------+----------+
在这里帮助！
推荐答案
您可以使用子查询选择最新的注释，然后选择JOIN:
You can use a subquery to select the most recent notes, and then JOIN:
select N.id, N.user_id, N.note_id, S.subject_name, T.tag_name, N.date, N.time
from (select N.*
from notes N
where N.user_id = 2
order by N.date desc, N.time DESC
limit 3
) N join
notes_subject S
on N.user_id = S.user_id and
N.note_id = S.note_id join
notes_tag T
on N.user_id = T.user_id and
N.note_id = T.note_id
order by N.date DESC, N.time DESC;
我猜想您需要最新的笔记，即使其他表格不匹配也是如此.为此，请使用left join s:
I am guessing that you want the most recent notes, even if the other tables do not match. For this, use left joins:
select N.id, N.user_id, N.note_id, S.subject_name, T.tag_name, N.date, N.time
from (select N.*
from notes N
where N.user_id = 2
order by N.date desc, N.time DESC
limit 3
) N left join
notes_subject S
on N.user_id = S.user_id and
N.note_id = S.note_id left join
notes_tag T
on N.user_id = T.user_id and
N.note_id = T.note_id
order by N.date desc, N.time desc;
如果只想使两个表匹配，请使用dense_rank():
If you want only notes that match both tables, use dense_rank():
select N.*
from (select N.id, N.user_id, N.note_id, S.subject_name, T.tag_name, N.date, N.time,
dense_rank() over (order by N.date desc, N.time desc, n.id) as seqnum
from notes N join
notes_subject S
on N.user_id = S.user_id and
N.note_id = S.note_id join
notes_tag T
on N.user_id = T.user_id and
N.note_id = T.note_id
) N
where seqnum <= 3
order by N.date desc, N.time desc;
这篇关于在SQL中进行JOIN后获取最后N条记录吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
