database - PostgreSQL 加入显示重复

Question

我的表连接有问题，当我连接时返回的结果是重复的。

表 A 字段:

id, name, acccount_number, account_number_other, customer_service_id, temporary, weight, created_at, updated_at

行:

1, X, 1234, 6543, 1, false, 5, '2017-12-12 19:51:06','2017-12-12 19:51:06'

表 B 字段:

id, name, account_number, branch_id, type, transaction, total, a_id, created_at, updated_at.

有行:

1, Y, 5232, 1, Z, 1000, 1000, 1, '2017-12-12 19:51:06', '2017-12-12 19:51:06'
2, Y, 5232, 1, Z, 1000, 1000, 1, '2017-12-12 20:11:00', '2017-12-12 20:11:00'

我的查询是:

SELECT a.id, a.name, a.weight, b.transaction, b.total, b.name as reference,
    b..type, b.id as reference_id, b.account_number
FROM a INNER JOIN b
    ON b.a_id = a.id
WHERE b.branch_id = 1 AND temporary = false
ORDER BY a.weight DESC, b.total DESC, b.transaction DESC;

但是，结果是重复的....因为表 B 上有 2 行与表 A 连接...我只想显示 1 行，条件只选择表 B 中最后创建的行。

Answer 1

使用ROW_NUMBER:

SELECT id, name, weight, transaction, total, reference, type, reference_id,
    account_number
FROM
(
    SELECT a.id, a.name, a.weight, b.transaction, b.total, b.name as reference,
        b.type, b.id as reference_id, b.account_number,
        ROW_NUMBER() OVER (PARTITION BY b.id ORDER BY b.created_at DESC) rn
    FROM a
    INNER JOIN b
         ON b.a_id = a.id
    WHERE b.branch_id = 1 AND temporary = false
) t
WHERE t.rn = 1
ORDER BY
    weight DESC, total DESC, transaction DESC;

对于表 B 中的每个 id 值，这将选择与最近的 created_at 值对应的记录。