我有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
请您参考如下方法:
您可以使用 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
)
-- 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
-- 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