DELETE a FROM psn a LEFT JOIN (SELECT a1.PID,a1.UID,COUNT(b.PID) FROM psn a1 INNER JOIN psn b ON a1.UID=b.UID AND a1.PID>=b.PID GROUP BY a1.PID,a1.UID HAVING COUNT(b.PID)<=2) b ON a.PId=b.pid AND a.uid=b.uid WHERE b.uid IS NULL;
如果不唯一,加入自增字段ID OR 用变量 SET @i=0; SET @j=''; DELETE a FROM psn a LEFT JOIN ( SELECT *,@i:=IF(@j=uid,@i+1,1) AS pm ,@j:=uid FROM psn) b ON a.uid=b.uid AND a.pid=b.pid AND b.pm<=2 WHERE b.pid IS NULL;
换个思路,查询只显示相同UID前2条记录怎么样?
感谢 wwwwb 和WWWWa另外从别的网友处找到个也不错的方法,而且这个方法我读起来也比较简单,总之,谢谢各位了!!DELETE FROM test WHERE pid IN ( SELECT max( t.pid ) FROM (SELECT pid, uid FROM test ) AS t, (SELECT uid FROM test GROUP BY uid HAVING count( * ) >2 ) AS t2 WHERE t.uid = t2.uid GROUP BY t.uid )
(SELECT a1.PID,a1.UID,COUNT(b.PID) FROM psn a1 INNER JOIN psn b ON a1.UID=b.UID AND a1.PID>=b.PID
GROUP BY a1.PID,a1.UID HAVING COUNT(b.PID)<=2) b ON a.PId=b.pid AND a.uid=b.uid
WHERE b.uid IS NULL;
SET @i=0;
SET @j='';
DELETE a FROM psn a LEFT JOIN (
SELECT *,@i:=IF(@j=uid,@i+1,1) AS pm ,@j:=uid FROM psn) b
ON a.uid=b.uid AND a.pid=b.pid AND b.pm<=2 WHERE b.pid IS NULL;
SELECT max( t.pid )
FROM (SELECT pid, uid
FROM test
) AS t, (SELECT uid
FROM test
GROUP BY uid
HAVING count( * ) >2
) AS t2
WHERE t.uid = t2.uid
GROUP BY t.uid
)