MySQL限制与许多到许多关系 [英] MySQL Limit with Many to Many Relationship

问题描述

给定用于实施标签的SCHEMA

Given a SCHEMA for implementing tags

ITEM
ItemId，ItemContent

ITEM ItemId, ItemContent

TAG
TagId，TagName

TAG TagId, TagName

ITEM_TAG
ItemId，TagId

ITEM_TAG ItemId, TagId

在使用标签选择时返回的ITEMS数量？

What is the best way to limit the number of ITEMS to return when selecting with tags?

SELECT i.ItemContent, t.TagName FROM item i 
INNER JOIN ItemTag it ON i.id = it.ItemId 
INNER JOIN tag t ON t.id = it.TagId 

当然是将它们全部返回的最简单的方法，但使用limit子句会分解，因为您会得到每个标记的所有项目的重复，其计入LIMIT中的行数。

is of course the easiest way to get them all back, but using a limit clause breaks down, because you get an duplicate of all the items for each tag, which counts toward the number of rows in LIMIT.

推荐答案

我的第二个解决方案使用MySQL函数GROUP_CONCAT（）将所有与该项匹配的标签组合成逗号分隔的字符串

My second solution uses a MySQL function GROUP_CONCAT() to combine all tags matching the item into a comma-separated string in the result set.

SELECT i.ItemContent, GROUP_CONCAT(t.TagName ORDER BY t.TagName) AS TagList
FROM item AS i 
  INNER JOIN ItemTag AS it ON i.id = it.ItemId 
  INNER JOIN tag AS t ON t.id = it.TagId
GROUP BY i.ItemId;

GROUP_CONCAT（）函数是MySQL功能，不是标准SQL的一部分。

The GROUP_CONCAT() function is a MySQL feature, it's not part of standard SQL.

这篇关于MySQL限制与许多到许多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！