oracle SQL DELETE 使用 DATE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18546543/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL DELETE using DATE
提问by user1667474
CUSTOMER(CustID, CustName)
Sale(SaleNo, StockNo, CustNo, SaleDate)
How do I delete customers that have not bought anything since 2009?
如何删除自 2009 年以来未购买任何东西的客户？
i can get the CustID's by using a minus
我可以通过使用减号来获取 CustID
SELECT CustID FROM CUSTOMER
WHERE SaleDate <= to_date('31-12-09', 'DD-MM-YY')
MINUS
SELECT CustID FROM CUSTOMER
WHERE SaleDate > to_date('31-12-09', 'DD-MM-YY');
But I have no idea how I would do the delete in a single query.
但我不知道如何在单个查询中进行删除。
Any suggestions will be appreciated
任何建议将不胜感激
回答by Luv
delete from CUSTOMER c
inner join Sale S
on C.CustID=S.CustNo
where max(SaleDate)<='31-12-09'
回答by a_horse_with_no_name
You didn't specify your DBMS although I suspect it's Oracle due to the use of MINUSand to_date()
您没有指定您的 DBMS 虽然我怀疑它是 Oracle 由于使用MINUS和to_date()
Anyway the following should work on Oracle (and any DBMS that complies with the ANSI standard)
无论如何，以下应该适用于 Oracle（以及任何符合 ANSI 标准的 DBMS）
delete from customer
where not exists (select 1
from sale
where sale.custId = customer.CustNo
and sale.saledate >= date '2009-01-01')
回答by Mudassir Hasan
In SQL Server
在 SQL Server 中
DELETE FROM Customer
WHERE CustID IN ( SELECT CustNo FROM Sale
GROUP BY CustNo
HAVING YEAR(MAX(SaleDate))<2009)
In MySQL
在 MySQL 中
DELETE FROM Customer
WHERE CustID IN ( SELECT CustNo FROM Sale
GROUP BY CustNo
HAVING EXTRACT(YEAR FROM MAX(SaleDate))<2009)
