1093 mysql 这条语句错的原因

DELETE FROM `test_tbale` WHERE `id` NOT  IN (SELECT   MAX(t.`id`) AS id FROM  `test_tbale`  t GROUP BY t.`ip` );

错误代码： 1093
You can\’t specify target table \’test_tbale\’ for update in FROM clause

DELETE FROM `test_tbale` WHERE `id` NOT  IN (
    SELECT m.`id` FROM (
        SELECT   MAX(t.`id`) AS id FROM  `test_tbale`  t GROUP BY t.`ip` )
    ) m
)

用一个中间表过渡下，据说可以，我没试过

FROM `test_tbale` WHERE `id` NOT IN (select a.id from (SELECT MAX(t.`id`) AS id FROM `test_tbale` t GROUP BY t.`ip` ) as a);/ps 这sql看着吓人，性能为0，最好不要小心被BOSS骂

如果id 是主键， not in里面的语句 不要需要带 group by 。
DELETE FROM #test WHERE id NOT IN ( SELECT max(id) FROM #test )

不能是同一张表

本文由风云小说网m.fenyunxs.com整理提供