时间戳

首页 > 解决方案 > MySQL：对添加的 JOIN 表使用 LIMIT

mysql - MySQL：对添加的 JOIN 表使用 LIMIT

问题描述

看法

该应用程序的视图包含一个显示一堆轮播的页面（每个轮播 =一个标签），每个都应包含 5 个产品：

+-------------------------------------+
|Page                                 |
|+-----------------------------------+|
||Tag1                               ||
||+--------+ +--------+    +--------+||
|||Product1| |Product2| .. |Product5|||
||+--------+ +--------+    +--------+||
|+-----------------------------------+|
|...                                  |
|+-----------------------------------+|
||TagN                               ||
||...                                ||
+-------------------------------------+

数据库

页面

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(16) | NO   | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

CREATE TABLE `pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

pages_tags

+--------------+------------+------+-----+---------+----------------+
| Field        | Type       | Null | Key | Default | Extra          |
+--------------+------------+------+-----+---------+----------------+
| id           | int(11)    | NO   | PRI | NULL    | auto_increment |
| page_id      | int(11)    | NO   | MUL | NULL    |                |
| tag_id       | int(11)    | NO   | MUL | NULL    |                |
| position     | tinyint(4) | NO   |     | NULL    |                |
+--------------+------------+------+-----+---------+----------------+

CREATE TABLE `pages_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `page_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  `position` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `page_id` (`page_id`),
  KEY `tag_id` (`tag_id`),
  CONSTRAINT `pages_tags_ibfk_1` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `pages_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=663 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

标签

+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(128) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

CREATE TABLE `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=149 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

产品标签

+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| product_id | int(11) | NO   | MUL | NULL    |                |
| tag_id     | int(11) | NO   | MUL | NULL    |                |
+------------+---------+------+-----+---------+----------------+

CREATE TABLE `products_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tag_id` (`tag_id`),
  KEY `bouquet_id` (`product_id`),
  CONSTRAINT `products_tags_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `products_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5698 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

产品

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(128) | NO   | UNI | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=335 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

目标

目标是获得一个包含页面上所有标签的输出，每个标签最多包含 5 个产品。

询问

这是我在卡住之前得到的查询：

SELECT t.name, p.name 
FROM pages a 
LEFT JOIN pages_tags a_t ON a_t.page_id = a.id 
LEFT JOIN tags t ON t.id = a_t.tag_id 
LEFT JOIN products p ON p.id = (
  SELECT p_t.product_id FROM products_tags p_t WHERE p_t.tag_id = t.id LIMIT 1
);`

问题

  1. 无法使用limit 5：

ERROR 1242 (21000)：子查询返回多于 1 行

  1. 无法使用in+ limit：

错误 1235 (42000): 这个版本的 MySQL 还不支持 'LIMIT & IN/ALL/ANY/SOME 子查询'

需要帮助。

我要返回的内容（示例）

标签： mysqlsqlgreatest-n-per-groupmysql-5.7

解决方案

您可以使用标量子查询来做您想做的事情：

p.id <= (SELECT p_t.product_id
         FROM products_tags p_t
         WHERE p_t.tag_id = t.id 
         ORDER BY p_t.product_id
         LIMIT 1 OFFSET 4
        )

但是，如果少于 5 行，这将不起作用。在这种情况下，您可以使用coalesce()：

p.id <= COALESCE( (SELECT p_t.product_id
                   FROM products_tags p_t
                   WHERE p_t.tag_id = t.id 
                   ORDER BY p_t.product_id
                   LIMIT 1 OFFSET 4
                  ), p.id
                )

注意：这假设您希望按顺序排列产品 ID。

推荐阅读