MySQL基于多返回值子查询的delete语句

Question

MySQL基于多返回值子查询的delete语句

这是我现在正在做的:

DELETE FROM `dnsstats` WHERE id NOT IN
(

 SELECT id FROM
    (
    SELECT id FROM `dnsstats` WHERE peerhost = 'x.x.x.243' ORDER BY id DESC LIMIT 500
    ) foo 
)
 AND id NOT IN
(
 SELECT id FROM
    (
    SELECT id FROM `dnsstats` WHERE peerhost = 'x.x.x.40' ORDER BY id DESC LIMIT 500
  ) foo2
)
 AND id NOT IN
(
 SELECT id FROM
    (
    SELECT id FROM `dnsstats` WHERE peerhost = 'x.x.x.50' ORDER BY id DESC LIMIT 500
  ) foo3
);

这很好用，但我想像这样动态选择各个 IP 地址:

SELECT peerhost FROM `dnsstats` GROUP BY peerhost;

..并根据这些返回值进行删除。

这是我尝试过的(但失败了):

DELETE FROM `dnsstats` WHERE id NOT IN
(
 SELECT id FROM
    (
    SELECT id FROM `dnsstats` WHERE peerhost = (
            SELECT peerhost FROM `dnsstats` GROUP BY peerhost;
        ) ORDER BY id DESC LIMIT 500
    ) foo 
);

知道我可以做些什么来完成这项工作吗？谢谢!

Answer 1

我认为您可能需要删除分号SELECT peerhost FROM dnsstats GROUP BY peerhost;

查询将运行，但我认为您不会得到正确的结果:早些时候，您从与 peerhost 相关的每个 id 中获得 500，

现在您将总共获得 500 个积分。您需要更改查询。

==> 编辑:试试这个布莱恩

DELETE FROM dnsstats WHERE id IN
(
    SELECT e.id FROM
    (
        SELECT d.id FROM dnsstats d
        LEFT JOIN
    (
            SELECT a.peerhost,
                (SELECT id FROM dnsstats WHERE peerhost = a.peerhost
                    ORDER BY id DESC LIMIT 499,1) id
            FROM 
            (SELECT peerhost FROM dnsstats GROUP BY peerhost) a
    ) c
    ON d.peerhost = c.peerhost
WHERE d.id < c.id) e);