多表关联与重复计数

查询文章作者、保留没有文章的用户,并正确统计文章数

🎯 引言

学完这篇文章,你能查询文章作者、保留没有文章的用户,并正确统计文章数。


📝 准备文章数据

用户表 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

建表与插入语句见 查询练习数据。接下来在 blog_demo 创建文章表:

CREATE TABLE article_sample (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(100) NOT NULL
);

user_id 保存作者在 query_user 中的编号,避免每篇文章重复保存姓名。

INSERT INTO article_sample VALUES
    (1, 1, '学习 SQL'), (2, 1, '认识查询'), (3, 2, '数据库笔记');

插入后,文章表 article_sample 的完整数据如下:

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

对照两张表:user_id 为 1 的两篇文章属于小明,为 2 的一篇属于小红;其他用户没有文章。这里先学习查询,关联数据的约束在数据设计篇处理。


🔗 INNER JOIN 查询文章及作者

SELECT a.title, u.username
FROM article_sample AS a
INNER JOIN query_user AS u ON a.user_id = u.id
ORDER BY a.id;

查询结果:

titleusername
学习 SQL小明
认识查询小明
数据库笔记小红

AS a、AS u 是表的简写名称;ON 指定哪两行可以配对。结果为“学习 SQL / 小明”“认识查询 / 小明”“数据库笔记 / 小红”。

INNER JOIN 只保留两边匹配的行。若某篇文章的 user_id 找不到用户,这篇文章不会出现在结果里。


👤 LEFT JOIN 保留没有文章的用户

SELECT u.username, a.title
FROM query_user AS u
LEFT JOIN article_sample AS a ON a.user_id = u.id
ORDER BY u.id, a.id;

查询结果:

usernametitle
小明学习 SQL
小明认识查询
小红数据库笔记
小刚NULL
小林NULL
小安NULL

左侧是 query_user,因此五位用户都会保留。小明对应两行文章,小红对应一行,其他三位用户各有一行 title 为 NULL,结果总共六行。

关联后的行数不一定等于用户数:一对多关系会为每个匹配组合生成一行。


🔢 统计每位用户的文章数

SELECT u.id, u.username, COUNT(a.id) AS article_count
FROM query_user AS u
LEFT JOIN article_sample AS a ON a.user_id = u.id
GROUP BY u.id, u.username ORDER BY u.id;

查询结果:

idusernamearticle_count
1小明2
2小红1
3小刚0
4小林0
5小安0

使用 COUNT(a.id),因为没有文章时 a.id 为 NULL,不会被计入。若写 COUNT(*),没有文章的用户那一行也会被计为 1。


🪤 右表条件放在哪里会改变结果

希望列出所有用户,但只显示标题为“学习 SQL”的文章:

SELECT u.username, a.title FROM query_user AS u
LEFT JOIN article_sample AS a
    ON a.user_id = u.id AND a.title = '学习 SQL'
ORDER BY u.id;

查询结果:

usernametitle
小明学习 SQL
小红NULL
小刚NULL
小林NULL
小安NULL

五位用户仍然保留,只有小明的 title 有值。条件放在 ON 中,决定哪些文章参与匹配。

如果把相同标题条件写在 WHERE 中,关联完成后的 NULL 行也会被排除,最终只剩小明。选择位置前,先决定要不要保留没有匹配文章的用户。


❓ 知识问答

Q1:两个表都有 id,怎么避免报字段不明确?

写 a.id 或 u.id,明确字段来自哪个表。

Q2:出现两行小明,是数据库重复存储了吗?

不是。本例是同一位用户匹配了两篇文章,查询结果有两行,用户表仍只有一条小明。

Q3:没有关联条件会怎样?

可能得到两表所有行的组合。应检查 ON 是否准确连接作者编号和用户主键。


🧪 小练习

查询每篇文章的标题及作者城市,再用 LEFT JOIN 找出文章数为 0 的用户,预期为小刚、小林、小安。

-- 请在这里编写 SQL 或记录验证结果