子查询与分组内取一条

查询符合关联条件的用户,并取出每位作者编号较大的文章

🎯 引言

学完这篇文章,你能查询符合关联条件的用户,并取出每位作者编号较大的文章。


🔎 EXISTS 只关心是否有匹配记录

本篇使用两张表。用户表 query_user:

idusernameagecitycreated_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:

iduser_idtitle
11学习 SQL
21认识查询
32数据库笔记

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;

查询结果:

idusername
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;

查询结果:

idusername
3小刚
4小林
5小安

只判断“有没有”时,这比先展开所有文章再去重更直接。


📏 用一个查询结果作为比较值

查询年龄高于已知平均年龄的人:

SELECT username, age FROM query_user
WHERE age > (SELECT AVG(age) FROM query_user)
ORDER BY id;

查询结果:

usernameage
小红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_idtotal
12

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_ididtitlern
12认识查询1
11学习 SQL2
23数据库笔记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_ididtitle
12认识查询
23数据库笔记

编号需要先算出来,才能在外层 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 或记录验证结果