1从2个选择查询中删除查询

I understand the title could be a bit vague, so I'll try to explain what I would like to do with a little example.

我理解标题可能有点模糊,所以我会尝试用一个小例子来解释我想做什么。

I have a table called students . I would like to delete all records from students that are multiple times in my table. So all records of Peter & Aaron have to be deleted.

我有一张叫做学生的桌子。我想从我的表中多次删除学生的所有记录。所以Peter&Aaron的所有记录都必须删除。

SELECT student, count(student) AS cnt FROM `testtable` GROUP BY `student` HAVING cnt > 1

I also would like to delete all records where mathematics is 8.

我还想删除数学为8的所有记录。

SELECT id FROM `testtable` WHERE mathematics = 8

But how do i come from these select querys to a delete query? And is it even possible to combine the 2??

但是,我如何从这些选择查询到删除查询?甚至可以将2?

Table: students

id   mathematics   biology   student 
--   -----------   -------   -------
0    6             8         Peter
1    6             8         Peter
2    3             9         Aaron
3    8             9         Alicia
4    1             4         Peter
5    7             7         Aaron
6    6             5         Rachel

3 个解决方案

#1

Thanks to Uueerdo, who pointed me to this direction, I got it to work.

感谢Uueerdo,他向我指出了这个方向,我得到了它的工作。

DELETE FROM testtable 
WHERE mathematics =8 
OR student IN (
    SELECT stu
    FROM (
        SELECT student AS stu
        FROM `testtable`
        GROUP BY `student`
        HAVING count( 1 ) >1
    )tmp
)

#2

Something like this might work (I say might because MySQL may not appreciate referencing the DELETE table in the subquery):

这样的东西可能会起作用(我说可能因为MySQL可能不喜欢在子查询中引用DELETE表):

DELETE FROM testable 
WHERE mathematics = 8 
OR student IN (
   SELECT student 
   FROM `testtable` 
   GROUP BY `student` 
   HAVING count(1) > 1
)
;

This way is longer, but pretty much guaranteed:

这种方式更长,但几乎可以保证:

CREATE TEMPORARY TABLE `t` 
  SELECT student, count(student) AS cnt 
  FROM `testtable` 
  GROUP BY `student` 
  HAVING cnt > 1
;
DELETE FROM testable 
WHERE mathematics = 8 
OR student IN ( SELECT student FROM `t` )
;
DROP TEMPORARY TABLE `t`;

#3

DELETE x
  FROM my_table x 
  JOIN my_table y 
    ON (y.student = x.student AND y.id <> x.id) 
    OR (y.id = x.id AND y.mathematics = 8);

#1