多表关联与重复计数
🎯 引言
学完这篇文章,你能查询文章作者、保留没有文章的用户,并正确统计文章数。
📝 准备文章数据
用户表 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 |
建表与插入语句见 查询练习数据。接下来在 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 的完整数据如下:
| id | user_id | title |
|---|---|---|
| 1 | 1 | 学习 SQL |
| 2 | 1 | 认识查询 |
| 3 | 2 | 数据库笔记 |
对照两张表: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;
查询结果:
| title | username |
|---|---|
| 学习 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;
查询结果:
| username | title |
|---|---|
| 小明 | 学习 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;
查询结果:
| id | username | article_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;
查询结果:
| username | title |
|---|---|
| 小明 | 学习 SQL |
| 小红 | NULL |
| 小刚 | NULL |
| 小林 | NULL |
| 小安 | NULL |
五位用户仍然保留,只有小明的 title 有值。条件放在 ON 中,决定哪些文章参与匹配。
如果把相同标题条件写在 WHERE 中,关联完成后的 NULL 行也会被排除,最终只剩小明。选择位置前,先决定要不要保留没有匹配文章的用户。
❓ 知识问答
Q1:两个表都有 id,怎么避免报字段不明确?
写 a.id 或 u.id,明确字段来自哪个表。
Q2:出现两行小明,是数据库重复存储了吗?
不是。本例是同一位用户匹配了两篇文章,查询结果有两行,用户表仍只有一条小明。
Q3:没有关联条件会怎样?
可能得到两表所有行的组合。应检查 ON 是否准确连接作者编号和用户主键。
🧪 小练习
查询每篇文章的标题及作者城市,再用 LEFT JOIN 找出文章数为 0 的用户,预期为小刚、小林、小安。
-- 请在这里编写 SQL 或记录验证结果
