选择具有一列的所有列具有不同的值

In my table,some records have all column values are the same, except one. I need write a query to get those records. what's the best way to do it? the table is like this:

在我的表中，一些记录的所有列值都相同，除了一个。我需要编写一个查询来获取这些记录。什么是最好的方法呢？表格是这样的：

 colA  colB colC
   a     b    c
   a     b    d
   a     b    e

What's the best way to get all records with all the columns? Thanks for everyone's help.

获取所有列的所有记录的最佳方法是什么？谢谢大家的帮助。

4 个解决方案

#1

Assuming you know that column3 will always be different, to get the rows that have more than one value:

假设您知道column3将始终不同，以获取具有多个值的行：

SELECT Col1, Col2
FROM Table t
GROUP BY Col1, Col2
HAVING COUNT(distinct col3) > 1

If you need all the values in the three columns, then you can join this back to the original table:

如果您需要三列中的所有值，则可以将其连接回原始表：

SELECT t.*
FROM table t join
     (SELECT Col1, Col2
      FROM Table t
      GROUP BY Col1, Col2
      HAVING COUNT(distinct col3) > 1
     ) cols
     on t.col1 = cols.col1 and t.col2 = cols.col2

#2

Just select those rows that have the different values:

只需选择具有不同值的行：

SELECT col1, col2
FROM myTable
WHERE colWanted != knownValue

If this is not what you are looking for, please post examples of the data in the table and the wanted output.

如果这不是您要查找的内容，请在表格中显示数据示例和所需输出。

#3

How about something like

怎么样的

SELECT Col1, Col2
FROM Table
GROUP BY Col1, Col2
HAVING COUNT(*) = 1

This will give you Col1, Col2 that have unique data.

这将为您提供具有唯一数据的Col1，Col2。

#4

Assuming col3 has the difs

假设col3有差异

SELECT Col1, Col2
FROM Table
GROUP BY Col1, Col2
HAVING COUNT(*) > 1

OR TO SHOW ALL 3 COLS

或者显示所有3种颜色

SELECT Col1, Col2, Col3
FROM Table1
GROUP BY Col1, Col2, Col3
HAVING COUNT(Col3) > 1

#1