删除所有行，除了在一个查询中表中有相关行的每个客户端的最后10行之外？

Question

所以我的情况是：

客户端表-有客户端数据等，不太令人兴奋

最近查看过的表，该表最近查看了客户端的内容，其结构如下：

( 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查询中这样做。

这是怎么做到的呢？谢谢

Answer 1

你可以这样做：

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

Answer 2

您可以使用vartiable来计算每个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            
)

Answer 3

乔戈斯的回答更快，但这里有另一个方法..。

考虑以下几点..。

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    |
+---+------+

反过来，...which也可以合并到删除中。这是一种简单的方法.

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提供的思路来看一个解决方案。