一个sql查询，用于获取5列相同但只有一列不同的所有记录

I have this table:

我有这张桌子:

A   B   C   D
1  Cat XYZ 12
1  Cat XYZ 13
1  Dog XYZ 14
2  Dog ABC 15
2  Dog ABC 16
3  Cat XYZ 17

Result set:

A   B   C   D
1  Cat XYZ 12
1  Cat XYZ 13
2  Dog ABC 15
2  Dog ABC 16

I need all such records in my table where A, B, C should be same and the D column can vary. Please help it soon that's why asking for help.

我需要在我的表中所有这些记录,其中A,B,C应该相同,D列可以变化。请尽快帮助,这就是寻求帮助的原因。

5 个解决方案

#1

You can use exists:

你可以使用exists:

select t.* 
from table t
where exists (select 1
              from table t1
              where t1.a = t.a and t1.b = t.b and t1.c = t.c and t1.d <> t.d
              );

#2

Given that you want one column to be different, you would need to decide on what value of Column D you want to be chosen.

鉴于您希望一列不同,您需要确定要选择的列D的值。

SELECT A, B, C, MAX(D) D
FROM table
GROUP BY A, B, C

In the above solution, I went the the maximum value of column D.

在上面的解决方案中,我去了D列的最大值。

#3

If you just want the a, b, c values, you can use aggregation:

如果您只想要a,b,c值,则可以使用聚合:

select a, b, c, min(d), max(d)
from t
group by a, b, c
having min(d) <> max(d);

If you want the actual rows, then Yogesh's solution with exists is probably the best approach, assuming none of the column values are NULL.

如果你想要实际的行,那么Yogesh的exists解决方案可能是最好的方法,假设没有列值为NULL。

#4

This should do it.

这应该做到这一点。

select * 
from ( select a, b, c, d, 
       count() over (partition by a, b, c) cnt
       from t1
     ) t 
where t.cnt > 1

#5

-1

Should be able to do this with a simple group by

应该可以通过一个简单的组来做到这一点

SELECT A, B, C, D
FROM table
GROUP BY A, B, C, D

#1