sql多对多查询仅在与数组中的所有项完全匹配时才产生结果

Question

我在mysql数据库上有三个表：

| pieces   | line_up       | instrument    | 
--------------------------------------------
| id_piece | piece_id      | id_instrument |
| title    | instrument_id | instrument    |

现在我想实现的是：我想查询那些lineup正是由列表中给出的乐器组成的作品，不是一个少一个多。这个sql查询将结果减少到那些只由这两种乐器演奏的乐曲，但它包括独奏

SELECT id_piece, title FROM pieces WHERE
(SELECT COUNT(*) FROM line_up WHERE line_up.piece_id = pieces.id_piece)
=
(SELECT COUNT(*) FROM line_up
INNER JOIN instruments ON instruments.id_instrument = line_up.instrument_id
WHERE line_up.piece_id = pieces.id_piece
AND instruments.instrument IN ('guitar', 'drums'));

例如这些表格：

| pieces               |  | line_up                  |  | instruments                |
-----------------------   ---------------------------   ------------------------------
| id_piece | title     |  | piece_id | instrument_id |  | id_instrument | instrument |
-----------------------  ----------------------------   ------------------------------
| 1        | hello     |  | 1        | 1             |  | 1             | guitar     |
| 2        | goodbye   |  | 1        | 2             |  | 2             | drums      |  
| 3        | goodnight |  | 2        | 1             |  ------------------------------
------------------------  | 3        | 2             |
----------------------------

吉他和鼓的唯一实际作品，因此我的查询结果应该是1 | hello。有什么建议吗？非常感谢。

Answer 1

您可以使用这样的聚合：

select p.id_piece, p.title
from pieces as p
inner join line_up as l on l.piece_id = p.id_piece
inner join instruments as i on l.instrument_id = i.id_instrument
group by p.id_piece
having sum(i.instrument in ('guitar', 'drums')) = 2
and sum(i.instrument not in ('guitar', 'drums')) = 0

如果您没有太多的工具可供搜索，则可以选择having子句中的字符串聚合：

having group_concat(i.instrument order by i.instrument) = 'drums,guitar'

第二个表达式要求为查询提供一个按字母顺序排列的仪器列表。

Answer 2

另一种方法。按乐器组合进行匹配。

SELECT title 
FROM pieces
WHERE id_piece IN (  
SELECT piece_id
FROM line_up 
WHERE instrument_id IN (
SELECT id_instrument
FROM instruments 
WHERE instrument IN ('Guitar', 'Drums')
)
GROUP BY piece_id
HAVING group_concat(instrument_id order by instrument_id separator ',') = (
SELECT group_concat(id_instrument order by id_instrument separator ',')
FROM instruments
WHERE instrument IN ('Guitar', 'Drums')
)
);

Answer 3

您需要计算使用仪器过滤的行数，并将其与仪器总数和所选仪器数进行比较。

db<gt；小提琴

with pieces as (
select 1 as id_piece, 'hello' as title union all
select 2 as id_piece, 'goodbye' union all
select 3 as id_piece, 'goodnight'
)
, line_up as (
select 1 as piece_id, 1 as instrument_id union all
select 1 as piece_id, 2 as instrument_id union all
select 2 as piece_id, 1 as instrument_id union all
select 3 as piece_id, 2 as instrument_id
)
, instruments as (
select 1 as id_instrument, 'guitar' as instrument union all
select 2 as id_instrument, 'drums' as instrument
)
select p.id_piece, p.title
from pieces as p
join line_up as l
on l.piece_id = p.id_piece
join instruments as i
on l.instrument_id = i.id_instrument
group by p.id_piece, p.title
having sum(case when i.instrument in ('guitar', 'drums') then 1 else 0 end) = count(1)
and count(1) = (
/*To select only valid instruments*/
select count(1)
from instruments as f
where f.instrument in ('guitar', 'drums')
)
-----------+--------
| id_piece | title |
|----------+-------|
|        1 | hello |
-----------+--------