我有ClientInvoice 表。它们具有一对多关系,其中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 

请您参考如下方法:

您可以使用 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 
) 

您还可以将 SUMCASE 一起使用或 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,但使用 CASEIF :

-- 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


评论关闭
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!