gpt4 book ai didi

sql - 只在表中保留用户的最后 5 个搜索结果

转载 作者:行者123 更新时间:2023-11-29 11:57:01 26 4
gpt4 key购买 nike

我需要将用户的最后 5 个搜索结果保存在一个表中。
我写了一个脚本来删除其他行,但它不起作用:

DELETE FROM         
SELECT
ROW_NUMBER () OVER (ORDER BY search_time DESC) AS row_number;
FROM
history_user
WHERE
user_id = 188
WHERE row_number>5

我做错了什么?

最佳答案

正确的语法 as detailed in the manual :

DELETE FROM history_user h
USING (
SELECT pk_id, row_number() OVER (ORDER BY search_time DESC) AS rn;
FROM history_user
WHERE user_id = 188
) sub
WHERE sub.rn > 5
AND h.pk_id = sub.pk_id;

pk_id唯一 的任何(组合)列。在您的情况下可能是 user_idsearch_time - 或者,更方便的是代理主键。

对于单个 user_id,您可以简化为:

DELETE FROM history_user h
USING (
SELECT pk_id
FROM history_user
WHERE user_id = 188
ORDER BY search_time DESC
OFFSET 5
) sub
WHERE h.pk_id = sub.pk_id;

另一方面,要同时处理多个用户,您需要将PARTITION BY 添加到您的窗口函数中:

DELETE FROM history_user h
USING (
SELECT pk_id, row_number() OVER (PARTITION BY user_id
ORDER BY search_time DESC) AS rn;
FROM history_user
) sub
WHERE sub.rn > 5
AND h.pk_id = sub.pk_id;

关于sql - 只在表中保留用户的最后 5 个搜索结果,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/26512509/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com