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 

如何从表中删除行“当前”的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

Answer 2

delete current 
where id in (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)