MySQL删除语句基于具有多个返回值的子选择 [英] MySQL delete statement based on sub-select with multiple return values

问题描述

基于多返回值的子选择的MySQL删除语句.

MySQL delete statement based on sub-select with multiple return values.

这是我现在正在做的:

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 地址:

This works great, but I would like to select the individual IP addresses dynamically like so:

SELECT peerhost FROM `dnsstats` GROUP BY peerhost;

..并根据这些返回值删除.

..and delete based on those returned values.

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

This is what i tried (and failed):

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 
);

知道我能做些什么来完成这项工作吗?谢谢！

Any idea what I can do to make this work? Thanks!

推荐答案

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

I think you might need to remove the semicolon in SELECT peerhost FROM dnsstats GROUP BY peerhost;

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

The query will run but I don't think you'll get the correct result: Earlier you were getting 500 from each id related to a peerhost,

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

now you'll be getting total 500 for all. You need to change the query.

==> 试试这个布莱恩

==> Try this Bryan

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);

这篇关于MySQL删除语句基于具有多个返回值的子选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！