所以我的情况是这样的：
客户表 - 有客户数据等，不太令人兴奋
最近查看的表 - 最近查看过客户端内容的表，其结构如下：
( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, client_id INT NOT NULL
, cookie_user_id INT NOT NULL
, hotel_id INT NOT NULL
, added DATETIME NOT NULL
, comment TEXT
,status TINYINT NOT NULL DEFAULE 1
);
我目前有一个部分工作的 SQL 来删除最近查看的表中的行，该行现在全局限制其中剩余的最新未删除记录的数量。这就是现在的样子
DELETE FROM `recently_viewed`
WHERE `recently_viewed`.`id` NOT IN (
SELECT id
FROM (
SELECT `id`
FROM `recently_viewed`
WHERE `client_id` IN (SELECT `id` FROM `klijenti`)
ORDER BY `id` DESC
LIMIT 5
) x
)
AND `client_id` <> 0
"限制 5"部分应限制 N 条记录保留在"每个客户端"的基础上最近查看的表中。现在，它将最近查看的表中的记录限制为 5，无论有多少客户端实际在那里有记录。因此，如果我有 10 个客户端，每个客户端在该表中都有 8 条记录，我希望此查询根据需要删除尽可能多的最旧记录，以便为每个客户端仅保留 5 个最近查看的最新项目，而不仅仅是在表中总共保留 5 个，忽略"每个客户端"逻辑。希望这对你有意义:)
目前，如果我首先获取应用程序中的所有客户端，然后执行 foreach 循环以为每个客户端进行另一个查询并留下他最近查看的 5 个项目，但希望在一个 SQL 查询中执行此操作，则此查询是可以的。
怎么能做到这一点？谢谢
你可以
这样做：
DELETE FROM `recently_viewed`
WHERE `recently_viewed`.`id` NOT IN (
SELECT id
FROM (
SELECT t.`id`,count(*) as rnk
FROM `recently_viewed` t
INNER JOIN `recently_viewed` s
ON(t.`client_id` = s.`client_id` and t.added <= s.added)
WHERE t.`client_id` IN (SELECT `id` FROM `klijenti`)
GROUP BY t.`ID`
) x
WHERE rnk <= 5
)
AND `client_id` <> 0
您可以使用变量来计算每个client_id最近的 5 条记录：
DELETE FROM `recently_viewed`
WHERE `recently_viewed`.`id` NOT IN
(
SELECT id
FROM (
SELECT `id`,
@rn := IF(@cid = `client_id`, @rn + 1,
IF(@cid := `client_id`, 1, 1)) AS rn
FROM `recently_viewed`
CROSS JOIN (SELECT @rn := 0, @cid := 0) AS vars
WHERE `client_id` IN (SELECT `id` FROM `klijenti`)
ORDER BY `client_id`, `id` DESC) x
WHERE x.rn <= 5
)
Giorgos 的回答更快，但这是另一种方法......
请考虑以下几点...
SELECT * FROM my_table ORDER BY x,i;
+---+------+
| i | x |
+---+------+
| 2 | A |
| 3 | A |
| 6 | A |
| 8 | A |
| 1 | B |
| 5 | B |
| 4 | C |
| 7 | C |
| 9 | C |
+---+------+
假设我们要为每个 x 选择两个最新的 i。这是一种方法
...SELECT m.* FROM my_table m JOIN my_table n ON n.x = m.x AND n.i >= m.i GROUP BY m.i HAVING COUNT(*) <= 2;
+---+------+
| i | x |
+---+------+
| 1 | B |
| 5 | B |
| 6 | A |
| 7 | C |
| 8 | A |
| 9 | C |
+---+------+
这个集合的反转可以如下找到。
SELECT m.* FROM my_table m JOIN my_table n ON n.x = m.x AND n.i >= m.i GROUP BY m.i HAVING COUNT(*) > 2;
+---+------+
| i | x |
+---+------+
| 2 | A |
| 3 | A |
| 4 | C |
+---+------+
。反过来可以合并到删除中。这是一个粗略的方法...
DELETE a FROM my_table a
JOIN
( SELECT m.* FROM my_table m JOIN my_table n ON n.x = m.x AND n.i >= m.i GROUP BY m.i HAVING COUNT(*) > 2 ) b
ON b.i = a.i;
Query OK, 3 rows affected (0.03 sec)
SELECT * FROM my_table ORDER BY x,i;
+---+------+
| i | x |
+---+------+
| 6 | A |
| 8 | A |
| 1 | B |
| 5 | B |
| 7 | C |
| 9 | C |
+---+------+
正如我所说，如果性能至关重要，那么请查看Giorgos提供的解决方案。