我有一個具有這種結構的表：
create table content_relations (
mainConId Integer not null,
relatedConId Integer not null,
similarity float not null,
relatedConAddedOn TIMESTAMP WITH TIME ZONE Not null);
現在我想要一個查詢，通過它我應該能夠使用以下條件從該表中洗掉行：
delete rows where count of same mainConId exceeds a max limit(say CMax),
i.e only keep CMax items per mainConId and that too with sorting according to similarity desc
i.e. keep only CMax items which are most similar in this table and remove extraneous records for every mainConId
所以在運行這個查詢之后，我應該在這個表中最多有 n*CMax 行，其中 n 是唯一的 mainConId 的數量。
有人可以幫我查詢嗎？我認為應該可以使用 postgres。感謝廣告。
uj5u.com熱心網友回復：
首先你可以試試這個：
WITH list AS
(
SELECT *
, row_number() OVER (PARTITION BY mainConId ORDER BY similarity DESC) AS row_number
FROM content_relations
)
SELECT *
FROM list AS l
WHERE row_number <= CMax
然后，如果結果對應于您要保留的行，則可以使用以下命令洗掉多余的行：
WITH list AS
(
SELECT mainConId
, similarity
, row_number() OVER (PARTITION BY mainConId ORDER BY similarity DESC) AS row_number
FROM content_relations
)
DELETE FROM content_relations AS cr
USING list AS l
WHERE cr.mainConId = l.mainConId
AND cr.similarity = l.similarity
AND l.row_number > CMax
uj5u.com熱心網友回復：
根據@Edouard H. 的回答，我達到了解決方案點：
WITH list AS
(
SELECT mainConId, relatedConId
, row_number() OVER (PARTITION BY mainConId ORDER BY similarity DESC) AS row_number
FROM content_relations
)
DELETE FROM content_relations AS cr
USING list AS l
WHERE cr.mainConId = l.mainConId
AND cr.relatedConId = l.relatedConId
AND l.row_number > CMax;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/352989.html
標籤：PostgreSQL 查询语句 删除行