MySQL按 "referencing"分组查询

Question

假设我有这个表stuff_property:

| stuff_id (fk) | property_id |
| ------------- | ----------- |

现在我想要进行查询，该查询将返回如下内容及其属性:

SELECT stuff_id
     , GROUP_CONCAT(DISTINCT property_id) as property_list
  FROM stuff_property 
 GROUP 
    BY stuff_id

但我只想列出具有某些属性的内容，比如 42。我不能简单地添加 WHERE property_id = 42 因为这样分组会“失败”并且 GROUP_CONCAT伤口没有列出所有属性，仅列出“42”。我能做的最好的事情就是使用嵌套查询，但效率非常低:

SELECT a.stuff_id, GROUP_CONCAT(DISTINCT a.property_id) as property_list
FROM stuff_property a GROUP BY a.stuff_id 
WHERE 42 IN 
    (SELECT b.property_id FROM stuff_property b WHERE b.stuff_id = a.stuff_id)

我想知道如何进行一些向后组引用，这样我就可以使查询有效，如下所示(组成伪代码):

SELECT stuff_id, GROUP_CONCAT(DISTINCT property_id) as property_list FROM stuff_property GROUP BY stuff_id WHERE 42 IN grouping-reference.property_id

有办法吗？

Answer 1

可以通过三种方式实现此目的:

使用子查询(被认为是效率最低的)

使用 Join(更好，但仍然低效)

SELECT stuff_id, GROUP_CONCAT(DISTINCT property_id) as property_list
  FROM stuff_property 
  INNER JOIN (SELECT stuff_ID FROM stuff_property where property_ID = 42) SubSet
 GROUP BY stuff_id

使用exists关键字(通常被认为是最有效的)

SELECT sp.Stuff_ID, GROUP_CONCAT(DISTINCT sp.property_id) as property_list
FROM stuff_property sp
  WHERE EXISTS (SELECT 1 FROM stuff_property s
                WHERE s.stuff_ID = sp.stuff_Id
                and s.property_ID=42);

在存在和连接中，您实质上是在说找到一组具有属性 ID 的内容。现在返回该子集中内容的所有属性。

就证明效率而言。这是基于网上的几篇文章和我自己的实践经验。但不要相信我或他们的话。查看所有这三个方面的执行计划，看看有什么问题困扰着您!

接下来的两个可能会起作用......

这使用 find_in_set 解析以逗号分隔的字符串以获取值

SELECT stuff_id, GROUP_CONCAT(DISTINCT property_id) as property_list
  FROM stuff_property 
WHERE find_in_set('42',GROUP_CONCAT(DISTINCT property_id)) > 0

这可能也有效，但性能又开始变得更加成问题..

Select * from (
Select stuff_ID, Group_Concat(Distinct property_ID) as Property_list      
FROM stuff_property) B
WHERE find_in_set('42',Property_List) > 0