mysql - SQL删除基于来自同一个表的查询的行？

Question

给出这个查询:

SELECT cur.`id`
FROM `current` AS cur
LEFT OUTER JOIN (
    SELECT MAX(EndDay_id.id) as id, EndDay_id.server_id
    FROM current as EndDay_id
    GROUP BY server_id, gameday
    ) AS EndDay
ON cur.id = EndDay.id
WHERE EndDay.id IS null

如何从表 'current' 中删除其 id 字段在上一个查询结果集中的行？

Answer 1

试试这个

DELETE cur FROM `current` AS cur
LEFT OUTER JOIN (
    SELECT MAX(EndDay_id.id) as id, EndDay_id.server_id
    FROM current as EndDay_id
    GROUP BY server_id, gameday
    ) AS EndDay
ON cur.id = EndDay.id
WHERE EndDay.id IS null

我测试了它，这应该可以工作:http://sqlfiddle.com/#!2/a47c81