我有三个字段，我想，以找出相同的唯一记录c3，并c1与不同c2。
我c1，c2和c3是列名。我需要找出与同c1和c3地方是c2不同的。
例如。
+-----+------+--------+
| c1 | c2 | c3 |
+-----+------+--------+
| A10 | Z10 | text1 |
| A10 | Z10 | text1 |
| A10 | Z40 | text2 |
| A10 | Z50 | text1 |
| A20 | Z100 | text1 |
| A20 | Z200 | text1 |
| A50 | Z10 | text1 |
| A60 | Z10 | text1 |
| A60 | Z10 | text1 |
+-----+------+--------+
我的输出要求来自重复的A10组C3
所以我需要：
+-----+-----+-------+
| c1 | c2 | c3 |
+-----+-----+-------+
| A10 | Z10 | text1 |
| A10 | Z50 | text1 |
+-----+-----+-------+
来自A20组的两者应C3相同且C2不同。
+-----+------+-------+
| c1 | c2 | c3 |
+-----+------+-------+
| A20 | Z100 | text1 |
| A20 | Z200 | text1 |
+-----+------+-------+
从A50和A60开始，输出中不应有任何内容，因为它们是唯一的或重复的。
最终输出为：
+-----+------+-------+
| c1 | c2 | c3 |
+-----+------+-------+
| A10 | Z10 | text1 |
| A10 | Z50 | text1 |
| A20 | Z100 | text1 |
| A20 | Z200 | text1 |
+-----+------+-------+
我试图找出重复的C3并应用了各种联接，但无法获得所需的结果。
这应该给您您需要的：
with t as
(
select
c1,
c3,
count(distinct c2) as n
from
YourTable
group by
c1,
c3
having
count(distinct c2) > 1
)
select distinct
YourTable.c1,
YourTable.c2,
YourTable.c3
from
YourTable
inner join
t
on
YourTable.c1 = t.c1
and YourTable.c3 = t.c3
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句