去重
A表:A B C D
1 ab a q
2 ab q a
3 ab e d
4 ab d e
5 pd n m
6 pd m n
7 pd v b
8 pd b v
9 cd f h
10 cd h f
目的是相当得到结果:
A B C D
1 ab a q
3 ab e d
5 pd n m
7 pd v b
9 cd f h
数据就是这样
还有就是表中只有BCD三个字段 后来将rowid作为主键(也就是这里的A列)
[解决办法]
使用分析函数LAG
select m.a, m.b, m.c, m.d
from (select a,
b,
c,
d,
concat(c, d) r,
lag(concat(d, c), 1, null) over(order by a) re
from t) m
where m.r <> m.re
or m.re is null
A B C D
---------- -- - -
1 ab a q
3 ab e d
5 pd n m
7 pd v b
9 cd f h
with t as(
select 1 A,'ab' B,'a' C,'q' D from dual
union all
select 2,'ab','q','a' from dual
union all
select 3,'ab','e','d' from dual
union all
select 4,'ab','d','e' from dual
union all
select 5,'pd','n','m' from dual
union all
select 6,'pd','m','n' from dual
union all
select 7,'pd','v','b' from dual
union all
select 8,'pd','b','v' from dual
union all
select 9,'cd','f','h' from dual
union all
select 10,'cd','h','f' from dual
)
select *
from t t2
where exists (select 1
from t t1
where t1.a > t2.a
and t1.b = t2.b
and greatest(t1.c, t1.d) = greatest(t2.c, t2.d)
and least(t1.c, t1.d) = least(t2.c, t2.d))
A B C D
---------- -- - -
1 ab a q
3 ab e d
5 pd n m
7 pd v b
9 cd f h