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 in (
SELECT peerhost FROM `dnsstats` GROUP BY peerhost
) ORDER BY id DESC LIMIT 500
) foo
);
. .但这给我留下了总共500条记录。我想保留每个peerhost
500条记录
你知道我该怎么做吗?谢谢!
原问题:MySQL delete语句基于子选择与多个返回值
我将使用变量来做到这一点。以下列举了每个peerhost
:
select s.*,
(@rn := if(@ph = peerhost, @rn + 1,
if(@ph := peerhost, 1, 1)
)
) as rn
from dnstats s cross join
(select @ph := '', @rn := 0) params
order by peerhost, id desc;
您可以在delete
中使用子查询:
delete s
from dnstats s join
(select s.*,
(@rn := if(@ph = peerhost, @rn + 1,
if(@ph := peerhost, 1, 1)
)
) as rn
from dnstats s cross join
(select @ph := '', @rn := 0) params
order by peerhost, id desc
) es
on es.id = s.id
where rn > 500;
这将删除每个peerhost
中除前500行以外的所有行。