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 
  

我如何得到Col 3 = 1的独特的行

我想得到第3和第6行（第2列分别是B和D）。我不想要A和C，因为他们也有Col 3 = 2。

我已经尝试了一些内容：

从表格组中选择第2列由第2列中的数字（第3列）= 1

<但是，这只会带来Col 2的结果，所以我不确定Col 3的内容是否为1。

编辑：大家也许我没有清楚地表达我的问题。我希望得到所有只包含Col 3 = 1 且只有1 的Col 2的行。

所以如果我试过WHERE Col 3 = 1 ，它会返回4行，因为A有1个。但是因为A也有一行Col 3 = 2，所以我不需要C从这个示例表中，我想要最终的结果只显示2行，B和D。

我的示例表是一个示例，实际上我有大约5000行过滤通过，否则我会做你们有建议：）

解决方案

  SELECT col2 
 FROM your_table 
 GROUP BY col2 
 HAVING MAX（col3）= 1 AND MIN（Col3）= 1 
  

$ b 或

  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）
  

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 unique rows which have 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.

I've tried something along the lines of:

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.

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.

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.

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

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)

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