mysql - SQL 连接子句返回多次出现的行而不分组

Question

我想从 JOIN 查询中提取多次出现的记录，但不对它们进行分组。

示例表:

tbl_names
id    Name
1     Mark
2     John
3     Jane
4     Paul

tbl_locations
id    Location
1     Berlin
2     Frankfurt
2     Stockholm
3     Helsinki
3     Madrid
3     London
4     Paris

ID 是一个外键。

现在，查询的结果将是:

id     Name    Location
2      John    Frankfurt
2      John    Stockholm
3      Jane    Helsinki
3      Jane    Madrid
3      Jane    London

即第一个表中的记录在 JOIN 子句结果中出现不止一次的所有 JOIN 记录。

我当然可以把它分组:

SELECT tbl_names.id, tbl_names.Name, tbl_locations.Location FROM tbl_names
 INNER JOIN tbl_locations ON (tbl_names.id = tbl_locations.id)
 GROUP BY tbl_names.id
 HAVING COUNT(tbl_names.id) > 1

我想要的是让它们完全不分组。我已经尝试使用子句和 NOT IN 但它非常慢并且没有给我想要的结果。

欢迎任何启发。

Answer 1

使用这个:

SELECT tbl_names.id, tbl_names.Name, tbl_locations.Location
FROM tbl_names
INNER JOIN tbl_locations ON (tbl_names.id = tbl_locations.id)
where tbl_names.id in (select id from tbl_locations
                       group by id having count(*) > 1);

此选择显示您已经拥有的连接，但仅选择名称/ID，它们在位置表中有超过 1 个条目。

根据 https://stackoverflow.com/a/3520552/1741542 ，这可能比普通子查询更快:

create view view_id_locations as
    select id
    from tbl_locations
    group by id
    having count(*) > 1;

SELECT tbl_names.id, tbl_names.Name, tbl_locations.Location
FROM tbl_names
INNER JOIN tbl_locations ON tbl_names.id = tbl_locations.id
inner join view_id_locations on tbl_names.id = view_id_locations.id;