子查询与分组内取一条
🎯 引言
学完这篇文章,你能查询符合关联条件的用户,并取出每位作者编号较大的文章。
🔎 EXISTS 只关心是否有匹配记录
本篇使用两张表。用户表 query_user:
| id | username | age | city | created_at |
|---|---|---|---|---|
| 1 | 小明 | 25 | 北京 | 2026-10-01 09:00:00 |
| 2 | 小红 | 30 | 上海 | 2026-10-02 09:00:00 |
| 3 | 小刚 | 22 | 北京 | 2026-10-03 09:00:00 |
| 4 | 小林 | 30 | 上海 | 2026-10-04 09:00:00 |
| 5 | 小安 | NULL | 杭州 | 2026-10-05 09:00:00 |
文章表 article_sample:
| id | user_id | title |
|---|---|---|
| 1 | 1 | 学习 SQL |
| 2 | 1 | 认识查询 |
| 3 | 2 | 数据库笔记 |
article_sample.user_id 对应 query_user.id。需要初始化时,分别使用 用户数据 和 文章数据 的建表与插入语句。
要查询“写过文章的用户”,不需要把每篇文章都展开:
SELECT u.id, u.username FROM query_user AS u
WHERE EXISTS (
SELECT 1 FROM article_sample AS a WHERE a.user_id = u.id
) ORDER BY u.id;
查询结果:
| id | username |
|---|---|
| 1 | 小明 |
| 2 | 小红 |
每人只出现一行。括号里是子查询,即另一条查询里面使用的查询;EXISTS 只判断是否找到至少一行。SELECT 1 的值不用于展示。
找出没写过文章的人,可以改用 NOT EXISTS:
SELECT u.id, u.username FROM query_user AS u
WHERE NOT EXISTS (
SELECT 1 FROM article_sample AS a WHERE a.user_id = u.id
) ORDER BY u.id;
查询结果:
| id | username |
|---|---|
| 3 | 小刚 |
| 4 | 小林 |
| 5 | 小安 |
只判断“有没有”时,这比先展开所有文章再去重更直接。
📏 用一个查询结果作为比较值
查询年龄高于已知平均年龄的人:
SELECT username, age FROM query_user
WHERE age > (SELECT AVG(age) FROM query_user)
ORDER BY id;
查询结果:
| username | age |
|---|---|
| 小红 | 30 |
| 小林 | 30 |
平均年龄是 26.75。此处括号里必须返回一个值;如果子查询返回多行,就不能直接放在这种大小比较中。
🏷 WITH 给中间结果取名字
先统计每位作者的文章数,再筛选至少两篇的作者:
WITH author_count AS (
SELECT user_id, COUNT(*) AS total
FROM article_sample GROUP BY user_id
)
SELECT user_id, total FROM author_count WHERE total >= 2;
查询结果:
| user_id | total |
|---|---|
| 1 | 2 |
WITH 定义的 author_count 是本条语句内可引用的查询结果,不是永久新建的表。它让“先统计、再筛选”的过程更容易阅读;简单统计直接使用 HAVING 也可以。
🥇 ROW_NUMBER 为每位作者的文章分别编号
现在要取“每位作者 id 较大的一篇文章”,还要保留标题。先查看编号结果:
SELECT user_id, id, title,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id DESC) AS rn
FROM article_sample ORDER BY user_id, rn;
查询结果:
| user_id | id | title | rn |
|---|---|---|---|
| 1 | 2 | 认识查询 | 1 |
| 1 | 1 | 学习 SQL | 2 |
| 2 | 3 | 数据库笔记 | 1 |
ROW_NUMBER 为行分配序号;PARTITION BY user_id 表示每位作者重新从 1 开始;ORDER BY id DESC 把编号较大的文章放前面。
这种给每行计算组内结果、又保留明细的功能叫窗口函数。它与 GROUP BY 的区别是不会把多篇文章合成一行。
🎯 在外层只取序号 1
WITH ranked AS (
SELECT user_id, id, title,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id DESC) AS rn
FROM article_sample
)
SELECT user_id, id, title FROM ranked WHERE rn = 1 ORDER BY user_id;
查询结果:
| user_id | id | title |
|---|---|---|
| 1 | 2 | 认识查询 |
| 2 | 3 | 数据库笔记 |
编号需要先算出来,才能在外层 WHERE 过滤,所以不能在计算 rn 的同一层直接写 WHERE rn = 1。
本例按 id 大小选择,不把自增编号等同于真实发布时间。业务要取较晚发布的文章时,应按发布时间排序,再加 id 处理时间相同的情况。
❓ 知识问答
Q1:只要作者编号,不需要标题,还要窗口函数吗?
不一定。按 user_id 分组计算 MAX(id) 就能得到每位作者较大的文章编号。
Q2:WITH 会永久保存结果吗?
不会,名称只在这一条语句内有效。需要长期保存应另行设计表。
Q3:NOT IN 与 NOT EXISTS 可以随意替换吗?
不可以。NOT IN 的子查询包含 NULL 时,可能得不到预期结果;判断不存在关联记录时通常用 NOT EXISTS 更清楚。
🧪 小练习
把 rn = 1 改成 rn <= 2,查询每位作者编号较大的两篇文章,检查小明是否返回两行。
-- 请在这里编写 SQL 或记录验证结果
