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
3
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
0
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
0
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
0
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
3
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
0
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
0
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
0
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