SQL 选择其中只有特定值的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6110565/
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 select rows with only a certain value in them
提问by MHTri
I have a table as such
我有一张这样的桌子
Col 1 Col 2 Col 3
1 A 1
2 A 2
3 B 1
4 C 1
5 C 2
6 D 1
How do I only get uniquerows which have Col 3 = 1?
如何只获得Col 3 = 1 的唯一行？
I want to get rows 3 and 6 (Col 2 = B and D respectively). I do not want A nor C since they have Col 3 = 2 as well.
我想获得第 3 行和第 6 行（分别为第 2 列 = B 和 D）。我不想要 A 或 C，因为它们也有 Col 3 = 2。
I've tried something along the lines of:
我尝试了以下方面的内容：
select col 2 from table group by col 2 having count(col 3) = 1
select col 2 from table group by col 2 having count(col 3) = 1
But that only brings up Col 2 for results so I'm uncertain if Col 3 contents = 1 or not.
但这只会带来第 2 列的结果，所以我不确定第 3 列的内容是否为 1。
EDIT:Sorry guys maybe I've not worded my question clearly. I want to get all of the rows of Col 2 which contain only Col 3 = 1 AND ONLY 1.
编辑：对不起，伙计们，也许我没有清楚地表达我的问题。我想获取仅包含 Col 3 = 1 AND ONLY 1的 Col 2 的所有行。
So if I tried WHERE Col 3= 1, it would return 4 rows because A has 1. But since A also has a row where Col 3 = 2, I do not want that, same for C. From this example table, I would want the end result to only show 2 rows, B and D.
因此，如果我尝试 WHERE Col 3= 1，它将返回 4 行，因为 A 有 1。但由于 A 也有一行 Col 3 = 2，我不想要那个，对于 C 也是如此。从这个示例表中，我想要最终结果只显示 2 行，B 和 D。
My example table is an example, I actually have about 5000 rows to filter through, otherwise I'd do as you guys have suggested :)
我的示例表是一个示例，我实际上有大约 5000 行要过滤，否则我会按照你们的建议做:)
采纳答案by Martin Smith
SELECT col2
FROM your_table
GROUP BY col2
HAVING MAX(col3) = 1 AND MIN(Col3) = 1
Or
或者
SELECT a.col2
FROM your_table a
WHERE a.col3=1 AND NOT EXISTS(SELECT *
FROM your_table b
WHERE a.col2=b.col2 AND b.col3<>1)
回答by Damian Leszczyński - Vash
回答by Mikael Eriksson
;with T ([Col 1], [Col 2], [Col 3]) as
(
select 1, 'A', 1 union all
select 2, 'A', 2 union all
select 3, 'B', 1 union all
select 4, 'C', 1 union all
select 5, 'C', 2 union all
select 6, 'D', 1
)
select *
from T
left outer join
(
select distinct [Col 2]
from T
where [Col 3] <> 1
) as T2
on T.[Col 2] = T2.[Col 2]
where T.[Col 3] = 1 and
T2.[Col 2] is null
回答by ic3b3rg
It's a bit hard to know exactly what you're trying to get, but this is my best guess:
确切地知道你想要得到什么有点困难，但这是我最好的猜测：
SELECT * FROM theTable WHERE col2 NOT IN
(SELECT col2 FROM theTable WHERE col3 <> 1)
回答by Paolo Falabella
SELECT * FROM #temp t1
WHERE EXISTS
(
select Col2 from #Temp t2
WHERE t2.Col2 = t1.Col2
group by col2
having count(col3) = 1
)
tested with MS SQL2008 and the following (so if my answer is not the correct one it may halp others test theirs...):
使用 MS SQL2008 和以下内容进行了测试（因此，如果我的答案不正确，它可能会阻止其他人测试他们的......）：
CREATE TABLE #temp
(
Col1 INT,
Col2 CHAR(1),
Col3 INT
)
INSERT INTO #Temp
(Col1, Col2, Col3)
SELECT 1,'A',1
UNION
SELECT 2,'A',2
UNION
SELECT 3,'B', 1
UNION
SELECT 4,'C',1
UNION
SELECT 5,'C',2
UNION
SELECT 6,'D',1
SELECT * FROM #temp t1
WHERE EXISTS
(
select Col2 from #temp t2
WHERE t2.Col2 = t1.Col2
group by col2
having count(col3) = 1
)
DROP TABLE #temp
