MySQL 查询返回值属于该组的所有组

Question

假设我有一个表contract_items。我的 contract_items 表具有字段 item_id 和 contract_id 。

我需要构建一个查询，从表中获取与给定项目具有相同 contract_id 的所有项目，并在 group_concat 中一起返回它们的 item_id 。该查询将具有类似于 where in 子句的内容，但相反，因为我需要基于单个 id 的整个组。

像这样的事情:

...
where item_id in item_ids

我该怎么做？我唯一能想到的是子查询，但这可能无法很好地扩展。

到目前为止我所取得的成就:

select group_concat(i.item_id SEPARATOR ', ')
from contract_items i
where i.contract_id = (
    select contract_id
    from contract_items
    where item_id = itemnum
)

如何使该查询更好(假设 itemnum 是给定的项目)？

Answer 1

使用having子句:

select i.contract_id, group_concat(i.item_id SEPARATOR ', ')
from contract_items i
group by i.contract_id
having sum( i.item_id = @itemnum ) > 0;

为了提高性能，您可能会发现子查询更好:

select i.contract_id, group_concat(i.item_id SEPARATOR ', ')
from contract_items i
where exists (select 1
              from contract_items i2
              where i2.contract_id = i.contract_id and
                    i2.item_id = @itemnum 
             )
group by i.contract_id;

这会在聚合之前进行过滤(性能增益)，并且可以在 contract_items(contract_id, item_id) 上使用索引。