Mysql多层子查询示例代码(收藏夹案例)

MySQL多层子查询是指在一个查询语句中嵌套了另一个查询语句，通常用于实现复杂查询需求。针对这个主题，我们来看一下一个收藏夹案例的示例代码。

示例代码

我们有三个数据表：用户表、文章表和收藏表。收藏表记录了用户收藏的文章列表。我们希望查询每个用户收藏的文章数量，以及收藏最多的文章对应的标题和作者。

首先，我们先创建三个数据表：

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `articles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `author` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `favorites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `article_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `article_id` (`article_id`),
  CONSTRAINT `favorites_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `favorites_ibfk_2` FOREIGN KEY (`article_id`) REFERENCES `articles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

然后，我们插入一些测试数据：

INSERT INTO `users` (`name`) VALUES ('Alice'), ('Bob'), ('Charlie');

INSERT INTO `articles` (`title`, `author`) VALUES
  ('Article 1', 'Author 1'),
  ('Article 2', 'Author 2'),
  ('Article 3', 'Author 3'),
  ('Article 4', 'Author 4'),
  ('Article 5', 'Author 5');

INSERT INTO `favorites` (`user_id`, `article_id`) VALUES
  (1, 1), (1, 2), (1, 3), (1, 4),
  (2, 2), (2, 3), (2, 5),
  (3, 1), (3, 4), (3, 5);

接下来，我们可以使用多层子查询来查询每个用户收藏的文章数量以及收藏数量最多的文章的标题和作者。在这个查询语句中，我们先使用子查询来计算每个用户收藏的文章数量，然后将此子查询作为另一个子查询的输入，通过与文章表关联找到最受欢迎的文章。GROUP BY 和 ORDER BY 子句用于分组和排序结果：

SELECT
  u.id,
  u.name,
  (
    SELECT COUNT(*)
    FROM `favorites` f
    WHERE f.user_id = u.id
  ) AS `favorite_count`,
  (
    SELECT a.title
    FROM `favorites` f
    LEFT JOIN `articles` a ON f.article_id = a.id
    WHERE f.user_id = u.id
    GROUP BY f.article_id
    ORDER BY COUNT(*) DESC
    LIMIT 1
  ) AS `most_popular_title`,
  (
    SELECT a.author
    FROM `favorites` f
    LEFT JOIN `articles` a ON f.article_id = a.id
    WHERE f.user_id = u.id
    GROUP BY f.article_id
    ORDER BY COUNT(*) DESC
    LIMIT 1
  ) AS `most_popular_author`
FROM `users` u;

运行上述代码，我们可以得到以下结果：

+----+---------+----------------+-------------------+--------------------+
| id | name    | favorite_count | most_popular_title | most_popular_author |
+----+---------+----------------+-------------------+--------------------+
|  1 | Alice   |              4 | Article 1         | Author 1           |
|  2 | Bob     |              3 | Article 2         | Author 2           |
|  3 | Charlie |              3 | Article 4         | Author 4           |
+----+---------+----------------+-------------------+--------------------+

我们可以看到，查询成功地找到了每个用户收藏的文章数量，以及收藏最多的文章的标题和作者。

示例说明

示例1：使用子查询计算每个用户收藏的文章数量

SELECT
  u.id,
  u.name,
  (
    SELECT COUNT(*)
    FROM `favorites` f
    WHERE f.user_id = u.id
  ) AS `favorite_count`
FROM `users` u;

在这个子查询中，我们针对每个用户的 ID 计算收藏记录数。在主查询中，我们通过使用子查询将此计算结果作为一个新列添加到查询结果中。

示例 2：使用多层子查询查找最受欢迎的文章

SELECT a.title
FROM `favorites` f
LEFT JOIN `articles` a ON f.article_id = a.id
WHERE f.user_id = u.id
GROUP BY f.article_id
ORDER BY COUNT(*) DESC
LIMIT 1

在这个子查询中，我们针对每个用户的 ID 查找其所收藏的所有文章，并对结果进行分组。分组后，我们按收藏记录数进行排序，找到收藏最多的文章。使用 LEFT JOIN 子句可以确保即使某个用户没有收藏任何文章，查询结果也可以返回空值。在主查询中，我们使用此子查询来找到每个用户收藏最多的文章的标题和作者。