从内部选择和按组获取行

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)

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

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