mysql - 查询具有特定条件的 Select Header Join Detail

Question

我有一个表头：

head-id | header-desc
------------------
   1   | Data 1
   2   | Data 2
   3   | Data 3
   4   | Data 4

和表格详细信息：

detail-id | head-id | detail-desc | category
----------------------------------
   D1     |   1     |      A      |   plant
   D2     |   1     |      B      |   plant
   D3     |   1     |      C      |   plant
----------------------------------
   D4     |   2     |      A      |   plant
   D5     |   2     |      B      |   plant
   D6     |   2     |      E      |   plant
----------------------------------
   D7     |   3     |      A      |   plant
   D8     |   3     |      B      |   plant
   D9     |   3     |      C      |   plant
----------------------------------
   D10    |   4     |      A      |   plant
   D11    |   4     |      B      |   plant
   D12    |   4     |      E      |   plant

我想获取由元素 detail-desc 'A'、'B'、'C' 组成的数据头。

我试过这个查询，但它返回所有数据。

select count(1), h.head-id from header h 
inner join detail d on h.head-id = d.head-id 
where d.category = 'plant'
and (d.detail-desc like '%A%' or d.detail-desc like '%B%' or d.detail-desc like '%C%')
group by d.head-id
having count(1) = 3;

标签： mysqlsql

Answer 1

你根本不需要 a join。我会期待这样的事情：

select count(1), d.head_id
from detail d 
where d.category = 'plant' and
      d.detail_desc in ('A', 'B', 'C')
group by d.head_id
having count(*) = 3;

请注意， nojoin​​是必需的，因为您的查询需要表之间的匹配。

这是一个 db<>fiddle。