英文:
Select field unless another field contains duplicate
问题
我已经创建了代表以下内容的示例数据（实际数据库每个记录有大约30个字段，在这个表中有60,000个记录）
| 客户编号 | 案例类型 | 案例状态 | 开启日期 | 结束日期 |
|---|---|---|---|---|
| 100 | 信件 | 信件 | 2023年01月01日 | 2023年01月03日 |
| 100 | 电话 | 电话 | 2023年01月02日 | 2023年01月04日 |
| 200 | 信件 | 信件 | 2023年01月01日 | 2023年01月03日 |
例如，我需要返回所有具有以下条件的“客户编号”：
- “案例类型 = 信件”
- “结束日期 = 2023年01月03日”
除非存在具有“案例类型 = 电话”的匹配“客户编号”，无论其他字段如何。
因此，从上述内容中，我将最终只得到一个记录，即“客户编号 = 200”，因为它具有“案例状态='信件'”而没有匹配的电话，并且于2023年01月03日关闭。
以下是我尝试过的内容：
SELECT DISTINCT Customer_ID
WHERE CaseType = '信件' AND DateClosed = '2023年01月03日'
显然，这会返回100和200，但我无法弄清“除非匹配的客户编号和案例类型 = 电话”部分。我假设这可能涉及“IF”命令，或者如果存在的话，可能涉及“NOT IF”。
任何提示/协助将不胜感激。
英文:
I've created sample data representing what I have below (actual database has about 30 fields per record and 60k records in this table)
| Customer_ID | CaseType | CaseStatus | DateOpened | DateClosed |
|---|---|---|---|---|
| 100 | Letter | Letter | 01/01/2023 | 01/03/2023 |
| 100 | Call | Call | 01/02/2023 | 01/04/2023 |
| 200 | Letter | Letter | 01/01/2023 | 01/03/2023 |
As an example, I need to return all "Customer_ID" that have:
- "CaseType = Letter"
- "DateClosed = 01/03/2023"
unless there is a matching "Customer_ID" that has a "CaseType = Call", regardless of any other fields.
So basically from the above I would end with only one record, that being "Customer_ID = 200" as it has "CaseStatus='Letter'" without a matching call, and it was closed on 01/03/2023.
Here's what I tried:
SELECT DISTINCT Customer_ID
WHERE CaseType = 'Letter' AND DateClosed = '01/03/2023'
which obvioulsy returns me both 100 and 200, but I cant figure out the "unless matching Customer_ID and CaseType = Call" part. I'm assuming it may involves the IF command, or NOT IF if that even exists.
Any pointers / assistance would be gratefully received.
答案1
得分: 0
你可以使用子查询的方式来实现：
SELECT DISTINCT
Customer_ID
FROM TABLE
WHERE CaseType = 'Letter'
AND DateClosed = '01/03/2023'
AND Customer_ID NOT IN (
SELECT Customer_ID
FROM TABLE
WHERE CaseType = 'Call'
)
这样你就排除了所有 CaseType 为 'Call' 的 Customer_ID。
在SQL中，IF 和 NOT IF 可以在 WHERE 语句中考虑。
英文:
You can do this way with a subquery:
SELECT DISTINCT
Customer_ID
FROM TABLE
WHERE CaseType = 'Letter'
AND DateClosed = '01/03/2023'
AND Customer_ID NOT IN (
SELECT
Customer_ID
FROM TABLE
WHERE CaseType = 'Call'
)
This way you are excluding all Customer_ID with a CaseType = 'Call'.
In SQL the IF and NOT IF can be thought in the WHERE statement.
答案2
得分: 0
你可能在寻找 NOT EXISTS 操作符，假设你的数据库管理系统支持它。
SELECT DISTINCT t1.Customer_ID
FROM tab t1
WHERE t1.CaseType = 'Letter' AND t1.DateClosed = '01/03/2023'
AND NOT EXISTS(SELECT 1 FROM tab t2 WHERE t2.CaseType = 'Call'
AND t1.CustomerID = t2.CustomerID)
英文:
You're probably looking for the NOT EXISTS operator, assuming your DBMS supports it.
SELECT DISTINCT t1.Customer_ID
FROM tab t1
WHERE t1.CaseType = 'Letter' AND t1.DateClosed = '01/03/2023'
AND NOT EXISTS(SELECT 1 FROM tab t2 WHERE t2.CaseType = 'Call'
AND t1.CustomerID = t2.CustomerID)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论