mysql - 选择每个 ID 存在的所有但/删除行

Question

我有一张 table :

表格

---

  RoomID   Name   Class   Gender
1  A       John    CS     Male
2  A       Mike    EE     Male
3  A       Mili    PD     Female
4  B       John    CS     Male
5  B       Mili    PD     Female
6  C       John    CS     Male

---

我想选择所有行，希望它们的(名称、类别)存在 x% 或更多 ID 的行，这意味着如果 x = 100 和某个(NameA, ClassA) 存在于表中的每个 ID，而不是过滤掉 Name = NameA 和 Class = ClassA 的行。

例如:如果 x = 100，则应删除第 6、4、1 行。 (John, CS) 每个 ID 都存在 - A、B、C。结果:

  RoomID   Name   Class   Gender
2  A       Mike    EE     Male
3  A       Mili    PD     Female
5  B       Mili    PD     Female

如果 x = 60，那么第 3,5 行现在也需要删除。 (Mili, PD) 对于 ID A、B 存在，因此对于 67% 的 ID 存在。结果:

  RoomID   Name   Class   Gender
2  A       Mike    EE     Male

*请注意，我没有表格以外的任何信息，在这种情况下，我只知道检查哪些列的值(名称、类别)。

非常感谢!

Answer 1

您可以使用给定的组合获得总房间 ID 的比例:

select name, class, count(*) as cnt, count(*) / numrooms as p
from t cross join
     (select count(distinct roomid) as numrooms from t) x
group by name, class;

然后您可以使用它进行删除:

delete t
     from t join
          (select name, class, count(*) as cnt, count(*) / numrooms as p
           from t cross join
                (select count(distinct roomid) as numrooms from t) x
           group by name, class
          ) tt
          on tt.name = t.name and tt.class = t.class
     where p >= 0.7;