mysql - 从内部选择和按组获取行

Question

学生表，包含 500,000- 1,000,000 行

    |-----------------|------------------|------------------|
    |    id           |     name         |    ref_id        |
    |-----------------|------------------|------------------|
    |         1       |        test      |    NULL          |
    |-----------------|------------------|------------------|       
    |          2      |        test      |    1             |
    |-----------------|------------------|------------------|         
    |           3     |         test3    |    1             |
    |-----------------|------------------|------------------|
    |           4     |         test4    |    NULL          |

以下情况如何查询:返回该行中的 id 不作为表的任何 ref_id 存在的行，并且仅当名称出现超过 1 时返回

如果表中的行 id = 2 没有在另一行中作为任何 ref_id 出现(“2”没有作为 ref_id 出现，我应该选择他，第二个条件是获取他的名字并检查 id 名称是否唯一或不)

因此在上面的示例中，要返回的行是 id 为 2 的行。因为它不显示为 ref_if 并且名称大于 1。

第 3 行不好，因为名称 test3 只出现了 1

我尝试过:

SELECT st.id FROM students at
WHERE at.id NOT IN (SELECT stt.ref_id FROM students stt)

Answer 1

首先获取id列表，该列表不存在于ref_id中

SELECT st.id FROM students at
WHERE NOT EXIST (SELECT * FROM students stt where stt.ref_id=at.id)

然后获取id列表，其中name重复多次

SELECT id, name FROM student n1
inner join student n2 on n2.name=n1.name where n1.id <> n2.id

现在，将这两个查询连接在一起

SELECT t1.id FROM
(SELECT st.id FROM students at
WHERE NOT EXIST (SELECT * FROM students stt where stt.ref_id=at.id)) t1
INNER JOIN
(SELECT id, name FROM student n1
inner join student n2 on n2.name=n1.name where n1.id <> n2.id
) t2 on t1.id=t2.id