MySQL 关联查询每个分类的前3条记录

在数据库设计和数据分析中，我们经常需要从多个表中提取相关信息，尤其是在面对分类数据时。例如，我们希望从一个在线商店的数据库中找到每个产品类别下销量最高的前三个产品。本文将介绍如何使用 MySQL 的关联查询来实现这个需求，并提供代码示例。

数据库设计

假设我们有两个表：products 和 categories。products 表存储商品信息，包括产品ID、名称、销量和类别ID，而 categories 表存储每个类别的详细信息，如ID和名称。

用表格表示：
products 表：
+----+-----------+---------+------------+
| id | name      | sales   | category_id|
+----+-----------+---------+------------+
| 1  | Product A | 100     | 1          |
| 2  | Product B | 150     | 1          |
| 3  | Product C | 200     | 2          |
| 4  | Product D | 250     | 2          |
| 5  | Product E | 50      | 1          |
| 6  | Product F | 80      | 2          |
+----+-----------+---------+------------+

categories 表：
+----+-------------+
| id | category_name|
+----+-------------+
| 1  | Category 1  |
| 2  | Category 2  |
+----+-------------+

关联查询实现

为了获取每个分类下销量最高的前三个产品，我们可以使用一个子查询来实现。首先，我们需要对 products 表中的数据按照销量进行排序，然后为每个分类分配一个排名，接着我们可以根据排名筛选出前三条记录。

以下是实现的 SQL 查询代码：

SELECT p.id, p.name, p.sales, c.category_name
FROM (
    SELECT id, name, sales, category_id,
           ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) as rank
    FROM products
) AS p
JOIN categories c ON p.category_id = c.id
WHERE p.rank <= 3;

代码解释

  1. 子查询：我们在子查询中使用 ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC)。PARTITION BY 用于为每个 category_id 分组，而 ORDER BY sales DESC 则是按照销量降序排列。通过这种方式，我们为每个产品分配了一个基于销量的排名。

  2. 主查询：在主查询中，我们从子查询中选择需要的字段，并通过 JOIN 语句将 products 表和 categories 表关联在一起，从而可以获取每个产品的类别名称。

  3. 筛选前3条记录：最后，我们在 WHERE 子句中指定 p.rank <= 3，以过滤出每个分类中的前三条记录。

结果展示

执行以上查询后，你将获得每个类别下销量最高的三款产品，包括它们的名称、销量以及对应的类别名称。这使得我们能够有效地分析产品的表现，并为决策提供数据支持。

结论

通过本示例，我们学习了如何使用 MySQL 进行关联查询，以获取每个分类的前3条记录。利用窗口函数和子查询的结合，我们可以高效地从复杂的数据集中提取我们需要的信息。此外，这种技术在各种数据分析场景中都非常实用，帮助我们更好地了解数据背后的故事。希望这篇文章能对你进一步掌握 SQL 查询有所帮助，欢迎继续深入学习数据库应用的技巧与方法！