mysql - 返回仅具有一个特定的多对多关系的记录

Question

鉴于此结构

CREATE TABLE locations
    (`id` int, `Name` varchar(128))
;

INSERT INTO locations
    (`id`, `Name`)
VALUES
    (1, 'Location 1'),
    (2, 'Location 2'),
    (3, 'Location 3')
;

CREATE TABLE locations_publications
    (`id` int, `publication_id` int, `location_id` int)
;

INSERT INTO locations_publications
    (`id`, `publication_id`, `location_id`)
VALUES
    (1, 1, 1),
    (2, 2, 1),
    (3, 2, 2),
    (4, 1, 3)
;

我只想查找位置 2，因为它与 Publication_id = 2 只有一个关系。

它不应该返回位置一，因为它有两个关系行。

这就是我正在寻找的东西，但当然不起作用，因为它限制了与 Publication_id = 2 的关系。

select * from locations
join locations_publications on locations_publications.location_id = locations.id
where locations_publications.publication_id = 2
group by (locations.location_id)
having count(*) = 1

Answer 1

您可以通过聚合来做到这一点:

select location_id
from locations_publications
group by location_id
having count(*) = 1

如果某个位置可能有多个包含同一出版物的记录，请将having条件更改为count(distinct Publication_id) = 1

<小时/>

根据您的编辑，您可以使用条件聚合:

select location_id
from locations_publications
group by location_id
having count(*) = sum(case when publication_id = 2 then 1 else 0 end)