排除特定记录的Mysql SQL查询

Question

我有一个具有外键prior_bill_id的bills表，它引用一个账单记录。对于每个账单，可以有一个或多个readings，有一个用于关联的联合表bill_readings。

| bills         |
| --------------|
| id            |
| prior_bill_id |

| readings |
| -------- |
| id       |

| bill_reaadings |
| ---------------|
| bill_id        |
| reading_id     |

我正在试着想出一个SQL来查找一个账单的所有读数，不包括给定账单的前一个账单的读数，这是我想出来的，如果有什么问题，请你纠正我好吗？提亚

    WITH CTE_readings AS (
      SELECT DISTINCT br.bill_id, br.reading_id
        FROM bills
        INNER JOIN bill_readings br ON br.bill_id=bills.id
    ), CTE_prior_readings AS (
      SELECT DISTINCT bills.id bill_id, pbr.reading_id
        FROM bills
        INNER JOIN bill_readings pbr ON pbr.bill_id=bills.prior_bill_id
    ) SELECT r.bill_id, r.reading_id
        FROM CTE_readings r
        LEFT OUTER JOIN CTE_prior_readings pr ON pr.reading_id=r.reading_id
        WHERE pr.bill_id IS NULL;

Answer 1

下面是我提出的查询，它可以按预期工作

    WITH CTE_readings AS (
      SELECT DISTINCT br.bill_id, br.reading_id
        FROM bill_readings br
    ), CTE_prior_readings AS (
      SELECT DISTINCT bills.id bill_id, pbr.reading_id
        FROM bills
        INNER JOIN bill_readings pbr ON pbr.bill_id=bills.prior_billing_id
    ) SELECT r.bill_id, r.reading_id
        FROM CTE_readings r
        WHERE NOT EXISTS (
          SELECT 1
            FROM CTE_prior_readings pr
            WHERE r.bill_id = pr.bill_id
              AND r.reading_id = pr.reading_id
        )