SQL 内连接 : ORDER BY LIMIT duplicates from one table

Question

我有 2 个表，它们之间没有相同的主键(id 存在于两个表中，主键仅用于表 A)。我想将第一个表 A 的主键用于 ON 子句。因此，我将从第二个表 B 中获得重复项。我想根据某些字段 B.cnt GROUP BY 重复项，并始终取第一个 - DESC LIMIT 1。

这是我尝试过的(DBMS 是 PostgreSQL):

SELECT 
    scheme1.A.some_attr, 
    B.some_attr
FROM 
    (SELECT * FROM scheme2.B ORDER BY scheme2.B.cnt DESC LIMIT 1) AS B
INNER JOIN
    scheme1.A
ON
    scheme1.A.id = B.id
;

查询返回单条记录。虽然所需的行为是仅针对 B 中具有相同 id 的每组记录返回单个记录(基于所提到的标准)。所以总的来说，查询当然会返回多条记录......

我怎样才能达到预期的效果？

谢谢，

Answer 1

使用rank()窗口分析函数，见Postgres Window Functions

SELECT * FROM (
                SELECT          
                scheme1.A.some_attr, 
                scheme2.B.some_attr,                     
                rank() OVER (PARTITION BY B.ID ORDER BY scheme2.B.cnt, scheme2.B.another_attr DESC) as rnk
                FROM 
                                scheme1.A
                INNER JOIN
                                scheme2.B
                ON
                                scheme2.B.id = scheme2.A.id
) A WHERE rnk = 1;