mysql - 如何从表 1 中检索在 MySQL 中表 2 中没有相关行的行

Question

我有一个有趣的问题 - 我需要 JOIN 语句的完全相反。

我有一个名为 invoices 的表和另一个名为 payments 的表。我想按顺序检索没有任何付款的发票，但之后是有付款的行。

发票表有这些字段:

• 编号
• 客户编号
• 时间戳

payments 表有这些字段:

• 编号
• 发票编号
• 数量

并非所有付款都是全额付款，因此每张发票可能附有多笔付款。

我希望首先显示未付发票(无付款)，然后是部分付款但未全额付款的发票，然后是全额付款的发票。

如果可以的话，我希望在执行此操作时不必运行单独的 SQL 查询 - 我宁愿它是一个查询，这样我就可以吐出一次。有任何想法吗？

-- 已更新--

根据 DCP 的回答，我更新了查询方法。我一直在工作，直到我需要添加该 JOIN 语句以按我的 sub_to_inv 表进行排序，如下所示:

id 订阅发票0 12 181 13 182 14 19

等

基于此我想到了这个(在末尾添加了 LIMIT ot limit by pages..)

SELECT i.id AS id, i.modify_date as modify_date, s.subscription as subscriptionid, 0 paidratio FROM invoices i
LEFT JOIN sub_to_inv s ON i.id=s.invoice
WHERE NOT EXISTS (SELECT id FROM payments p WHERE p.invoice=i.id) AND i.corporation='3' AND i.payer=1

UNION ALL
SELECT i.id AS id, i.modify_date as modify_date, s.subscription as subscriptionid, p.paid/i.total AS paidratio FROM invoices i,
(SELECT p.invoice, sum(amount) AS paid FROM payments p GROUP BY p.invoice) p
LEFT JOIN sub_to_inv s ON i.id=s.invoice
WHERE p.invoice=i.id AND i.corporation='3' AND i.payer=1

AND p.paid UNION ALL
SELECT i.id AS id, i.modify_date as modify_date, s.subscription as subscriptionid, p.paid/i.total AS paitratio FROM invoices i,
(SELECT p.invoice, sum(amount) AS paid FROM payments p WHERE p.invoice=id GROUP BY p.invoice) p
LEFT JOIN sub_to_inv s ON i.id=s.invoice
WHERE p.invoice = i.id AND i.corporation='3' AND i.payer=1

AND p.paid=i.total

ORDER BY paidratio DESC, modify_date DESC, subscriptionid ASC LIMIT 0,40

我收到一个“未找到 i.id 列”错误，但我没有收到。在最终通过 UNION 语句组合的每个语句 block 上，我指定 invoices i 以确保 i.id 指的是发票的 id。 tab.e

Answer 1

您没有告诉我们如何确定总发票金额，但我假设您的发票表上有一些字段与此相关。在下面的示例中，我假设它称为 TOT_AMT。

这种方法背后的想法是，我们通过三个独立的步骤获取数据，然后使用 UNION ALL 将这三个部分粘合在一起。我们使用一个名为 PAY_TYPE 的伪列来获得您想要的顺序，即首先是没有付款的发票，然后是部分付款的发票，最后是全额付款的发票。

下面显示了一个完整的工作示例(我使用的是 Oracle，但我认为您可以轻松地将其适应 MySQL)。

CREATE TABLE invoices (ID number, tot_amt NUMBER);
CREATE TABLE payments (ID NUMBER, invoice_id NUMBER, amount NUMBER);

INSERT INTO invoices VALUES (1,100);
INSERT INTO invoices VALUES (2,200);
INSERT INTO invoices VALUES (3,300);

INSERT INTO payments VALUES (1,1,20);
INSERT INTO payments VALUES (2,1,40);

INSERT INTO payments VALUES (3,3,150);
INSERT INTO payments VALUES (4,3,120);
INSERT INTO payments VALUES (5,3,30);

/* get invoices with no payments */
SELECT i.id
     , i.TOT_AMT
     , 0               TOT_PAID
     , '0_NO_PAYMENTS' PAY_TYPE
  FROM invoices i
  WHERE NOT EXISTS (SELECT * FROM PAYMENTS P WHERE P.INVOICE_ID = I.ID)
UNION ALL
/* get invoices with partial payments */
SELECT i.id
     , i.TOT_AMT
     , p.tot_paid
     , '1_PARTIALLY_PAID' PAY_TYPE
  FROM invoices i
     , (SELECT P.INVOICE_ID
             , SUM(AMOUNT) tot_paid
          FROM PAYMENTS P
         GROUP BY P.INVOICE_ID) p
 WHERE P.INVOICE_ID = I.ID
   AND p.TOT_PAID < i.TOT_AMT
UNION ALL
/* get invoices that are fully paid */
SELECT i.id
     , i.TOT_AMT
     , p.tot_paid
     , '2_FULLY_PAID' PAY_TYPE
  FROM invoices i
     , (SELECT P.INVOICE_ID
             , SUM(AMOUNT) tot_paid
          FROM PAYMENTS P
         GROUP BY P.INVOICE_ID) p
 WHERE P.INVOICE_ID = I.ID
   AND p.TOT_PAID = i.TOT_AMT
ORDER BY PAY_TYPE   

结果:

    ID      TOT_AMT   TOT_PAID   PAY_TYPE
    2       200       0          0_NO_PAYMENTS
    1       100       60         1_PARTIALLY_PAID
    3       300       300        2_FULLY_PAID