第 5 章 · 多表连接与子查询

SELECT id, title, category_id FROM article;

SELECT a.id, a.title, a.author, c.name AS 分类
FROM article a
JOIN category c ON a.category_id = c.id
ORDER BY a.id;

-- 以 category 为左表：5 个分类一条不少
SELECT c.name AS 分类, a.title AS 作品
FROM category c
LEFT JOIN article a ON a.category_id = c.id
ORDER BY c.id, a.id;

-- 以 category 为右表：右边的 5 个分类一条不少
SELECT c.name AS 分类, a.title AS 作品
FROM article a
RIGHT JOIN category c ON a.category_id = c.id
ORDER BY c.id, a.id;

-- 6 篇作品一条都不能少；没书评的作品，书评列为 NULL
SELECT a.id, a.title, cm.content AS 书评
FROM article a
LEFT JOIN comment cm ON cm.article_id = a.id
ORDER BY a.id, cm.id;

-- ① 种子里 3 条书评的 parent_id 都是 NULL，先插一条临时回复
INSERT INTO `comment` (`user_id`, `article_id`, `parent_id`, `content`)
VALUES (2, 1, 1, '同感，尤其最后一句。');

-- ② 自连接：把「回复」和它回复的那条评论配成一对
SELECT r.id AS 回复ID, r.content AS 回复,
       p.id AS 被回复ID, p.content AS 被回复
FROM `comment` r
JOIN `comment` p ON r.parent_id = p.id;

-- 仍然是自连接，这次用 LEFT JOIN：没有回复的评论也要显示为 0
SELECT p.id AS 评论ID, p.content AS 评论, COUNT(r.id) AS 回复数
FROM `comment` p
LEFT JOIN `comment` r ON r.parent_id = p.id
GROUP BY p.id, p.content
ORDER BY p.id;

-- 5 个分类 × 2 个用户 = 10 行
SELECT c.name AS 分类, u.nickname AS 用户
FROM category c
CROSS JOIN user u
ORDER BY c.id, u.id;

-- UNION：合并后自动去掉重复行 → 3 行
SELECT title FROM article WHERE category_id = 1
UNION
SELECT title FROM article WHERE dynasty = '唐';

-- UNION ALL：不去重，两个结果原样叠起来 → 5 行
SELECT title FROM article WHERE category_id = 1
UNION ALL
SELECT title FROM article WHERE dynasty = '唐';

-- 种子里的 3 条书评分别评的是作品 1、1、3 → 内层结果只有 1 和 3
SELECT id, title
FROM article
WHERE id IN (SELECT article_id FROM comment)
ORDER BY id;

-- 6 篇作品平均浏览量 = (328+256+412+298+187+203)/6 ≈ 280.67
SELECT title, views
FROM article
WHERE views > (SELECT AVG(views) FROM article)
ORDER BY views DESC;

-- 对每个作品，检查 comment 里有没有 article_id 等于它的记录
SELECT a.id, a.title
FROM article a
WHERE EXISTS (SELECT 1 FROM comment c WHERE c.article_id = a.id)
ORDER BY a.id;

SELECT c.id, c.content, 
       (SELECT u.nickname FROM user u WHERE u.id = c.user_id) AS 评论人
FROM comment c
ORDER BY c.id;

-- views > 298 即可（比宋词里「随便一篇」高就行）
SELECT title, views
FROM article
WHERE views > ANY (SELECT views FROM article WHERE dynasty = '宋')
ORDER BY views DESC;

-- views >= 412 才行（要「比宋词里每一篇」都高）
SELECT title, views
FROM article
WHERE views >= ALL (SELECT views FROM article WHERE dynasty = '宋');

-- 反过来找「比宋词每一篇都低」的：
-- WHERE views < ALL (SELECT views FROM article WHERE dynasty = '宋')

-- 分类名用 JOIN 取，书评数用「列里的标量子查询」现算
SELECT a.id,
       a.title,
       c.name AS 分类,
       (SELECT COUNT(*) FROM comment cm WHERE cm.article_id = a.id) AS 书评数
FROM article a
LEFT JOIN category c ON a.category_id = c.id
ORDER BY a.id;