MySQL 根据 id 选择所有具有非不同值的行

Question

请参阅下表。如果数字不明显，我想列出所有带有名称和数字的行

表1

+------+-------+
|ID    |Name   |
+------+-------+
|1     |A      |
+------+-------+
|2     |A      |
+------+-------+
|3     |A      |
+------+-------+
|4     |B      |
+------+-------+
|5     |B      |
+------+-------+

表2

+------+-------+
|ID    |Number |
+------+-------+
|1     |1234   |
+------+-------+
|2     |123    |
+------+-------+
|3     |1234   |
+------+-------+
|4     |456    |
+------+-------+
|5     |456    |
+------+-------+

我尝试过以下查询。这将列出所有具有非连续数字的名称，但它只会给我第一行。

SELECT table1.Name, table2.Number
FROM table1, table2
WHERE table1.ID = table2.ID
GROUP BY table1.Name having count(distinct table2.Number) > 1;

+------+-------+
|Name  |Number |
+------+-------+
|A     |1234   |
+------+-------+

我想要的是如果同一名称上有不同的数字，则列出所有行。

+------+-------+
|Name  |Number |
+------+-------+
|A     |1234   |
+------+-------+
|A     |123    |
+------+-------+
|A     |1234   |
+------+-------+

Answer 1

您可以通过将两个表连接在一起并再次连接到子查询来完成您想要的操作，该子查询标识具有大于 1 的不同数字计数的名称。

SELECT t1.Name, t2.Number
FROM table2 t2
INNER JOIN table1 t1
    ON t2.ID = t1.ID
INNER JOIN
(
    SELECT t1.Name
    FROM table2 t2
    INNER JOIN table1 t1
        ON t2.ID = t1.ID
    GROUP BY t1.Name
    HAVING COUNT(DISTINCT t2.Number) > 1    -- # distinct numbers > 1
) t3
    ON t1.Name = t3.Name                    -- restrict to matching names only

此处演示:

SQLFiddle