例如,我们有:
<表类>
id
价值
relation_id
tbody><<tr>1 value1 1 2value2 2 3 value3 1 4value4 1 5value5 3 表类>
方法1:使用PostgreSQL的DISTINCT ON
运算符。在给定ORDER BY
子句的情况下,它将只匹配括号内字段的不同值,以防止重复。
SELECT DISTINCT ON(relation_id) id_, value_, relation_id
FROM tab
ORDER BY relation_id
点击这里查看演示。
方法2:使用ROW_NUMBER
窗口函数生成一个对您的记录按">relation_id"分区的排名,然后为每个关系(rownum = 1)选择第一个记录
WITH cte AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY relation_id ORDER BY id_) AS rn
FROM tab
)
SELECT id_, value_, relation_id
FROM cte
WHERE rn = 1
点击这里查看演示。
方法3:在过滤构造FETCH FIRST ROWS WITH TIES
中使用ROW_NUMBER
,它将充当方法2,但避免了子查询(获得绑定行,绑定在rownum=1上)。
SELECT *
FROM tab
ORDER BY ROW_NUMBER() OVER(PARTITION BY relation_id ORDER BY id_) = 1 DESC
FETCH FIRST 1 ROWS WITH TIES
点击这里查看演示。