sql - 在联接表中仅匹配每条记录一次

Question

我有两张 table 。第一个 inv 包含发票记录，第二个包含 payments。我想通过 inv_amount 和 inv_date 匹配 inv 表中的付款。同一天可能有不止一张相同金额的发票，也可能有不止一笔相同金额的付款。

付款应与第一张匹配的发票匹配，且每笔付款只能匹配一次。

这是我的数据:

表inv

 inv_id | inv_amount |  inv_date  | inv_number
--------+------------+------------+------------
      1 |         10 | 2018-01-01 |          1
      2 |         16 | 2018-01-01 |          1
      3 |         12 | 2018-02-02 |          2
      4 |         14 | 2018-02-03 |          3
      5 |         19 | 2018-02-04 |          3
      6 |         19 | 2018-02-04 |          5
      7 |          5 | 2018-02-04 |          6
      8 |         40 | 2018-02-04 |          7
      9 |         19 | 2018-02-04 |          8
     10 |         19 | 2018-02-05 |          9
     11 |         20 | 2018-02-05 |         10
     12 |         20 | 2018-02-07 |         11

表支付

 pay_id | pay_amount |  pay_date
--------+------------+------------
      1 |         10 | 2018-01-01
      2 |         12 | 2018-02-02
      4 |         19 | 2018-02-04
      3 |         14 | 2018-02-03
      5 |          5 | 2018-02-04
      6 |         19 | 2018-02-04
      7 |         19 | 2018-02-05
      8 |         20 | 2018-02-07

我的查询:

 SELECT DISTINCT ON (inv.inv_id) inv.inv_id,
    inv.inv_amount,
    inv.inv_date,
    inv.inv_number,
    pay.pay_id
   FROM ("2016".pay
     RIGHT JOIN "2016".inv ON (((pay.pay_amount = inv.inv_amount) AND (pay.pay_date = inv.inv_date))))
  ORDER BY inv.inv_id

导致:

 inv_id | inv_amount |  inv_date  | inv_number | pay_id
--------+------------+------------+------------+--------
      1 |         10 | 2018-01-01 |          1 |      1
      2 |         16 | 2018-01-01 |          1 |
      3 |         12 | 2018-02-02 |          2 |      2
      4 |         14 | 2018-02-03 |          3 |      3
      5 |         19 | 2018-02-04 |          3 |      4
      6 |         19 | 2018-02-04 |          5 |      4
      7 |          5 | 2018-02-04 |          6 |      5
      8 |         40 | 2018-02-04 |          7 |
      9 |         19 | 2018-02-04 |          8 |      6
     10 |         19 | 2018-02-05 |          9 |      7
     11 |         20 | 2018-02-05 |         10 |
     12 |         20 | 2018-02-07 |         11 |      8

记录 inv_id = 6 不应与 pay_id = 4 匹配，因为这意味着付款 4 被插入了两次

期望的结果:

inv_id | inv_amount |  inv_date  | inv_number | pay_id
--------+------------+------------+------------+--------
      1 |         10 | 2018-01-01 |          1 |      1
      2 |         16 | 2018-01-01 |          1 |
      3 |         12 | 2018-02-02 |          2 |      2
      4 |         14 | 2018-02-03 |          3 |      3
      5 |         19 | 2018-02-04 |          3 |      4
      6 |         19 | 2018-02-04 |          5 |        <- should be empty**
      7 |          5 | 2018-02-04 |          6 |      5
      8 |         40 | 2018-02-04 |          7 |
      9 |         19 | 2018-02-04 |          8 |      6
     10 |         19 | 2018-02-05 |          9 |      7
     11 |         20 | 2018-02-05 |         10 |
     12 |         20 | 2018-02-07 |         11 |      8

免责声明:是的，我昨天用原始数据问了这个问题，但有人指出我的 sql 很难阅读。因此，我试图创建一个更清晰的问题表示。

为方便起见，这里有一个要测试的 SQL Fiddle:http://sqlfiddle.com/#!17/018d7/1

Answer 1

在看到示例后，我想我已经得到了您的查询:

WITH payments_cte AS (
    SELECT
        payment_id,
        payment_amount,
        payment_date,
        ROW_NUMBER() OVER (PARTITION BY payment_amount, payment_date ORDER BY payment_id) AS payment_row
    FROM payments
), invoices_cte AS (
    SELECT
        invoice_id,
        invoice_amount,
        invoice_date,
        invoice_number,
        ROW_NUMBER() OVER (PARTITION BY invoice_amount, invoice_date ORDER BY invoice_id) AS invoice_row
    FROM invoices
)
SELECT invoice_id, invoice_amount, invoice_date, invoice_number, payment_id
FROM invoices_cte
LEFT JOIN payments_cte
    ON payment_amount = invoice_amount
    AND payment_date = invoice_date
    AND payment_row = invoice_row
ORDER BY invoice_id, payment_id