mysql菜鸟教程

首页 >> mysql菜鸟教程

专栏导航

20.4 复杂查询实现

在博客系统的数据库中，我们已经创建了表并插入了初始数据。本节将通过一系列典型且具有一定复杂度的查询，演示如何运用多表连接、子查询、聚合函数、分组、窗口函数等技术，从数据库中提取有价值的信息。这些查询涵盖了内容展示、统计分析和性能优化等多个方面，是实际开发中非常实用的技能。

一、基础统计查询

1. 每个分类下的文章数量（包含无文章的分类）

SELECT 
    c.id,
    c.name AS category_name,
    COUNT(p.id) AS article_count
FROM categories c
LEFT JOIN posts p ON c.id = p.category_id AND p.status = 'published'
GROUP BY c.id
ORDER BY article_count DESC;

解释：

使用 LEFT JOIN 确保即使某个分类下没有已发布的文章，也能显示出来（计数为0）。
在 JOIN 条件中加上 p.status = 'published'，只统计已发布的文章；若需要统计所有状态，可移除该条件。
按文章数量降序排列，最热门的分类在前。

2. 每个标签下的文章数量（按使用次数排序）

SELECT 
    t.id,
    t.name AS tag_name,
    COUNT(pt.post_id) AS usage_count
FROM tags t
LEFT JOIN post_tags pt ON t.id = pt.tag_id
GROUP BY t.id
ORDER BY usage_count DESC;

解释：

左连接确保未被使用的标签也会显示（计数0）。
结果按使用次数从高到低排序，可直观了解热门标签。

3. 每个作者（用户）的文章总数、评论总数

SELECT 
    u.id,
    u.username,
    u.nickname,
    COUNT(DISTINCT p.id) AS post_count,
    COUNT(c.id) AS comment_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
LEFT JOIN comments c ON u.id = c.user_id
GROUP BY u.id
ORDER BY post_count DESC, comment_count DESC;

解释：

同时左连接 posts 和 comments 表，统计每个用户的文章数和评论数。
使用 COUNT(DISTINCT p.id) 避免因多表连接导致的重复计数（例如一个用户有多个评论和多篇文章，连接后会产生笛卡尔积）。
注意：这种写法在数据量大时可能效率较低，可考虑分别统计再合并，但此处演示简单方法。

二、文章详情查询

4. 查询文章列表，包含作者名、分类名、标签列表（使用 GROUP_CONCAT）

SELECT 
    p.id,
    p.title,
    p.excerpt,
    p.views,
    p.published_at,
    u.username AS author,
    c.name AS category,
    (SELECT GROUP_CONCAT(t.name SEPARATOR ', ') 
     FROM post_tags pt 
     JOIN tags t ON pt.tag_id = t.id 
     WHERE pt.post_id = p.id) AS tags
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.status = 'published'
ORDER BY p.published_at DESC
LIMIT 10;

解释：

主查询获取文章基本信息、作者和分类。
子查询使用 GROUP_CONCAT 将文章的所有标签合并为一个逗号分隔的字符串，方便显示。
LEFT JOIN 分类，因为文章可能未分类。
筛选已发布的文章，按发布时间倒序取前10条。

5. 查询最热门的文章（按浏览量排序）

SELECT 
    p.id,
    p.title,
    p.views,
    u.username AS author,
    c.name AS category,
    p.published_at
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.status = 'published'
ORDER BY p.views DESC
LIMIT 5;

解释：

直接按 views 字段降序排列，取前5篇。
可结合 WHERE 筛选已发布文章。

6. 查询每篇文章的最新评论（或评论数量）

方法一：每条文章显示最近一条评论

SELECT 
    p.id,
    p.title,
    c.content AS latest_comment,
    c.created_at AS comment_time,
    u.username AS commenter
FROM posts p
LEFT JOIN comments c ON p.id = c.post_id
LEFT JOIN users u ON c.user_id = u.id
WHERE (c.created_at IS NULL OR c.created_at = (
    SELECT MAX(created_at) 
    FROM comments 
    WHERE post_id = p.id
))
ORDER BY p.id;

解释：

使用相关子查询找到每篇文章的最新评论时间，然后匹配出该条评论。
若文章无评论，则 latest_comment 为 NULL。

方法二：统计每篇文章的评论数量

SELECT 
    p.id,
    p.title,
    COUNT(c.id) AS comment_count
FROM posts p
LEFT JOIN comments c ON p.id = c.post_id
GROUP BY p.id
ORDER BY comment_count DESC;

7. 查询最近发布的5篇文章及其作者、分类

SELECT 
    p.title,
    p.published_at,
    u.username AS author,
    c.name AS category
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.status = 'published'
ORDER BY p.published_at DESC
LIMIT 5;

三、高级统计分析

8. 使用窗口函数对文章按浏览量排名（MySQL 8.0+）

SELECT 
    p.id,
    p.title,
    p.views,
    u.username AS author,
    RANK() OVER (ORDER BY p.views DESC) AS rank_overall,
    DENSE_RANK() OVER (PARTITION BY c.id ORDER BY p.views DESC) 
AS rank_in_category
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.status = 'published';

解释：

RANK() OVER (ORDER BY views DESC) 计算全局排名，相同浏览量会并列并留下空隙。
DENSE_RANK() OVER (PARTITION BY category_id ORDER BY views DESC) 计算在每个分类内部的排名，相同浏览量并列但不留空隙。
窗口函数只能在 MySQL 8.0 及以上版本使用。

9. 查询每个分类下浏览量最高的文章

SELECT 
    c.name AS category,
    p.title,
    p.views,
    u.username AS author
FROM (
    SELECT 
        category_id,
        MAX(views) AS max_views
    FROM posts
    WHERE status = 'published' AND category_id IS NOT NULL
    GROUP BY category_id
) AS max_per_category
JOIN posts p ON p.category_id = max_per_category.category_id 
            AND p.views = max_per_category.max_views
            AND p.status = 'published'
JOIN categories c ON p.category_id = c.id
JOIN users u ON p.user_id = u.id;

解释：

子查询先找出每个分类的最大浏览量。
主查询连接 posts 表找到浏览量等于该最大值的文章（注意：如果存在并列第一，会返回多行）。
若分类下没有文章，则不会出现在结果中。

10. 查询没有评论的文章

SELECT 
    p.id,
    p.title,
    u.username AS author
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON p.id = c.post_id
WHERE c.id IS NULL;

解释：

使用左连接，并筛选右表主键为 NULL 的行，即可找出没有评论的文章。

11. 查询发布了超过2篇文章的作者

SELECT 
    u.id,
    u.username,
    COUNT(p.id) AS post_count
FROM users u
JOIN posts p ON u.id = p.user_id
GROUP BY u.id
HAVING COUNT(p.id) > 2;

解释：

HAVING 对分组后的结果进行过滤，只保留文章数大于2的作者。

四、全文搜索

12. 查找标题或内容中包含关键词的文章（使用全文索引）

SELECT 
    p.id,
    p.title,
    p.excerpt,
    MATCH(p.title, p.content) AGAINST('MySQL 索引' IN NATURAL LANGUAGE MODE) 
AS relevance
FROM posts p
WHERE MATCH(p.title, p.content) AGAINST('MySQL 索引' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

解释：

前提是已在 posts 表的 (title, content) 上创建了全文索引（如我们之前所做的 FULLTEXT INDEX ft_title_content (title, content)）。
MATCH() AGAINST() 返回相关度评分，可用于排序。
使用 IN NATURAL LANGUAGE MODE 进行自然语言搜索，也可使用 IN BOOLEAN MODE 实现更复杂的布尔搜索（如包含、排除等）。

五、查询优化建议

使用索引：确保 WHERE、JOIN、ORDER BY 子句中涉及的列有合适的索引。例如，我们已经在 posts 表的 user_id、category_id、status 等列上建立了索引。
避免在 WHERE 条件中对列使用函数：如 WHERE DATE(published_at) = '2025-01-01' 会导致无法使用索引，应改为 published_at >= '2025-01-01' AND published_at < '2025-01-02'。
限制返回行数：使用 LIMIT 分页，避免一次返回过多数据。
对复杂查询使用 EXPLAIN 分析执行计划，找出潜在的性能瓶颈。

六、小结

本节通过12个具体查询示例，展示了如何运用SQL的各种高级特性从博客数据库中提取信息。这些查询涵盖了多表连接、子查询、聚合函数、窗口函数和全文搜索，是实际开发中经常会遇到的需求。掌握这些技巧，你将能够灵活应对各种复杂的数据分析任务。