SQL 在可能有多个值的列中选择只有一个值的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21708896/
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
Select Records With Only One Value In A Column Where Multiple Are Possible
提问by user3298276
My apologies in advance, this is probably a basic question asked and answered but I don't know how to word the search to find the right results.
我提前道歉，这可能是一个基本问题，但我不知道如何用搜索词来找到正确的结果。
I have a table that (among other columns) contains program names for a customer number. I need to identify customers that have only one specific program and no others. A simplified example:
我有一个表（在其他列中）包含客户编号的程序名称。我需要识别只有一个特定程序而没有其他程序的客户。一个简化的例子：
Col1 = Customer_Number, Col2 = Program_Name
Col1 = Customer_Number，Col2 = Program_Name
Customer 1 has three records because they are enrolled in 2013BA1111, 2013BO1161 and 2013BO1163. Customer 2 has just one record because they are only enrolled in 2013BA1111.
客户 1 有 3 条记录，因为他们注册了 2013BA1111、2013BO1161 和 2013BO1163。客户 2 只有一条记录，因为他们只注册了 2013BA1111。
Using Teradata SQL Assistant, if I select WHERE Program_Name = '2013BA1111', both Customer 1 and Customer 2 will be returned since they are both enrolled in program 2013BA1111. I want to select only Customer 2 since they have ONLY 2013BA1111.
使用 Teradata SQL 助手，如果我选择 WHERE Program_Name = '2013BA1111'，则客户 1 和客户 2 都将被返回，因为他们都注册了程序 2013BA1111。我只想选择客户 2，因为他们只有 2013BA1111。
Thanks!
谢谢！
采纳答案by Nicholas Carey
In standard (ANSI/ISO) SQL, a derived tableis your friend. Here, we join the customer table against a derived table that produces the list of customers having only 1
在标准 (ANSI/ISO) SQL 中，派生表是您的朋友。在这里，我们将客户表与派生表连接起来，该表产生只有 1
select *
from customer c
join ( select customer_id
from customer
group by customer_id
having count(program_name) = 1
) t on t.customer_id = c.customer_id
where ... -- any further winnowing of the result set occurs here
回答by Aaron Palmer
Perhaps something like this:
也许是这样的：
select Customer_Number, Program_Name
from YourTable t1
left join (
select Customer_Number
from YourTable t2
where t2.Program_Name <> '2013BA1111'
) t3 on t1.Customer_Number = t3.Customer_Number
where
t1.Program_Name = '2013BA1111'
and t3.Customer_Number is null
The outer query is selecting all records that have the given Program_Name, then it is joined with an inner query of everyone who has a record that does not equal the given Program_Name, and the outer query checks to make sure that the joined inner query doesn't have a match.
外部查询选择具有给定 的所有记录Program_Name，然后将其与具有不等于给定记录的每个人的内部查询连接Program_Name，并且外部查询检查以确保连接的内部查询没有一场比赛。
回答by b.runyon
I'm unfamiliar with "Teradata", but you should be able to do it in sql with something like:
我不熟悉“Teradata”，但是您应该可以在 sql 中使用以下内容进行操作：
SELECT
Col1 AS Customer,
COUNT(*) AS TotalOccurences
FROM
YourTable
GROUP BY Col1
HAVING COUNT(*) = 1
回答by OlleR
This would do it in SQL server. Not sure if Teradata is the same...
这将在 SQL 服务器中完成。不确定 Teradata 是否相同...
select t.Col1, min(t.Col2) from myTable t
group by t.Col1
having count(*) = 1
回答by dnoeth
Do you need only the customer_number or additional columns?
您是否只需要 customer_number 或其他列？
SELECT Customer_Number
FROM tab
GROUP BY 1
HAVING SUM(CASE WHEN Program_Name = '2013BA1111' THEN 0 ELSE 1 END) = 1;
SELECT *
FROM tab AS t1
WHERE Program_Name = '2013BA1111'
AND NOT EXISTS
(
SELECT * FROM tab AS t2
WHERE t1.Customer_Number = t2.Customer_Number
AND Program_Name <> '2013BA1111'
);
SELECT *
FROM tab
QUALIFY
SUM(CASE WHEN Program_Name = '2013BA1111' THEN 0 ELSE 1 END)
OVER (PARTITION BY Customer_Number) = 1;
