SQL 选择其中只有特定值的行 [英] SQL select rows with only a certain value in them

问题描述

我有一张这样的桌子

Col 1 Col 2 Col 3
1       A     1
2       A     2
3       B     1
4       C     1
5       C     2
6       D     1

如何只获得第 3 列 = 1 的唯一行?

How do I only get unique rows which have Col 3 = 1?

我想获得第 3 行和第 6 行(第 2 列分别 = B 和 D).我不想要 A 或 C，因为它们也有 Col 3 = 2.

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.

我已经尝试了以下方法:

I've tried something along the lines of:

选择 col 2 from table group by col 2 with count(col 3) = 1

但这只会显示第 2 列的结果，所以我不确定第 3 列的内容是否为 1.

But that only brings up Col 2 for results so I'm uncertain if Col 3 contents = 1 or not.

对不起，大家可能我没有清楚地表达我的问题.我想获取仅包含 Col 3 = 1 AND ONLY 1 的 Col 2 的所有行.

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.

因此，如果我尝试 WHERE Col 3= 1，它将返回 4 行，因为 A 有 1.但由于 A 也有一行 Col 3 = 2，我不想要那样，相同对于 C.从这个示例表中，我希望最终结果只显示 2 行，B 和 D.

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.

我的示例表是一个示例，我实际上有大约 5000 行要过滤，否则我会按照你们的建议做:)

My example table is an example, I actually have about 5000 rows to filter through, otherwise I'd do as you guys have suggested :)

推荐答案

SELECT col2
FROM your_table
GROUP BY col2
HAVING MAX(col3) = 1 AND MIN(Col3) = 1

或

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)

这篇关于SQL 选择其中只有特定值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！