mysql - 选择日期后没有发票的客户

Question

我有Client 和Invoice 表。它们具有一对多关系，其中Client.id = Invoice.client_id。

客户专栏:

• 编号

发票列:

• 身份证，
• client_id,
• 发票日期

当然这个例子被简化为相关数据。

我正在尝试选择在“2010-01-01”之后没有有发票的客户。

我想不出任何可行的方法来做到这一点。我走的一些路线看起来像这样(还有许多其他变化，但没有必要在这里显示):

SELECT c.id, COUNT(i.invoice_date > "2010-01-01") AS cnt 
 FROM Client AS c LEFT JOIN Invoice i ON i.client_id = c.id 
 GROUP BY c.id HAVING cnt = 0

和

SELECT client_id, COUNT(invoice_date > '2010-01-01') as cnt 
FROM Invoice
GROUP BY client_id HAVING cnt = 0

Answer 1

您可以使用 NOT EXISTS 的子查询像这样:

SELECT *
FROM Client
WHERE NOT EXISTS (
  SELECT 1
  FROM Invoice 
  WHERE Invoice.invoice_date > '2010-01-01' AND Invoice.client_id = Client.id
)

您还可以将 SUM 与 CASE 一起使用或 IF :

-- CASE
SELECT c.id, SUM(CASE WHEN i.invoice_date > '2010-01-01' THEN 1 ELSE 0 END) AS cnt 
FROM Client AS c LEFT JOIN Invoice i ON i.client_id = c.id 
GROUP BY c.id
HAVING cnt = 0

-- IF
SELECT c.id, SUM(IF(i.invoice_date > '2010-01-01', 1, 0)) AS cnt 
FROM Client AS c LEFT JOIN Invoice i ON i.client_id = c.id 
GROUP BY c.id
HAVING cnt = 0

您也可以使用 COUNT，但使用 CASE或 IF :

-- CASE
SELECT client_id, COUNT(CASE WHEN invoice_date > '2010-01-01' THEN 1 ELSE NULL END) as cnt 
FROM Invoice
GROUP BY client_id HAVING cnt = 0

-- IF
SELECT client_id, COUNT(IF(invoice_date > '2010-01-01', 1, NULL)) as cnt 
FROM Invoice
GROUP BY client_id HAVING cnt = 0

demo on dbfiddle.uk