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列)
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列)
解决方案 »
- java写oracle数据库出现Execution of query failed,求高手解答
- 字符型数据转换为日期型数据
- 一个字段多条记录,如果进行and 查询
- 各位有经验的DBA麻烦帮我诊断一下我的oracle
- 请教个PLSQL调试的问题
- oracel触发器,每隔一天触发一次?谁有示例?急用.
- PL/SQL 奇怪的问题
- 请大侠们帮我改改这个简单的存储过程
- 请问用ASP怎么取出ORACLE 中的序列值?
- 删除数据量大时,sqlplus死掉,数据删不掉,该怎么办?
- 求一个oracle能用的存储过程,给你一个月份求出包含该月的所有天数的记录!例如给你2012-12那你就返回1号到31号的查询记录!
- 数据库数据完整对比问题求教~~
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
或者你可以使用ROWNUM虚拟一个列出来。
这个很灵活了
后来把A字段改为了NUMBER类型的 去重就没有问题了
关于rownum虚拟列的问题,这个疏忽了 只钻到了rowid上 将rowid虚拟了一列 忘记还有rownum 多谢大神啊 哈哈
目前字段类型依次为:NUMBER VARCHAR2 VARCHAR2 VARCHAR2 是不是LAG里面的参数不合适啊 能不能稍微说下那个参数的值啊 以前没见到过这个函数 谢谢了
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
,不过这种方法算是学习了
你不要直接用ROWNUM,我的你可以这样不用ROWNUM,TO_NUMBER(AREA_KEY)这样.
(select 1 as a, 'ab' as b, 'a' as c, 'q' as d
from dual
union all
select 2 as a, 'ab' as b, 'q' as c, 'a' as d
from dual
union all
select 3 as a, 'ab' as b, 'e' as c, 'd' as d
from dual
union all
select 4 as a, 'ab' as b, 'd' as c, 'e' as d
from dual
union all
select 5 as a, 'pd' as b, 'n' as c, 'm' as d
from dual
union all
select 6 as a, 'pd' as b, 'm' as c, 'n' as d
from dual
union all
select 7 as a, 'pd' as b, 'm' as c, 'n' as d
from dual
union all
select 8 as a, 'pd' as b, 'm' as c, 'n' as d
from dual
union all
select 9 as a, 'pd' as b, 'n' as c, 'm' as d
from dual
union all
select 10 as a, 'cd' as b, 'm' as c, 'n' as d
from dual
union all
select 11 as a, 'ab' as b, 'a' as c, 'q' as d 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))
order by a结果: A B C D
---------- -- - -
1 ab a q
2 ab q a
3 ab e d
5 pd n m
6 pd m n
7 pd m n
8 pd m n已选择7行。
SELECT A, B, C, D
FROM (select A,
B,
C,
D,
min(A) keep(DENSE_RANK first ORDER BY A) over(partition by B, C, D) AS A2
FROM T T1
WHERE EXISTS (SELECT 1
FROM T T2
WHERE T2.A > T1.A
AND T2.C = T1.D
AND T2.D = T1.C))
WHERE A = A2
FROM (select A,
B,
LEAST(T1.C, T1.D) AS C,
GREATEST(T1.C, T1.D) AS D,
min(A) keep(DENSE_RANK first ORDER BY A) over(partition by B, GREATEST(C, D), LEAST(C, D)) AS A2
FROM T T1)
WHERE A = A2================================================
1 3 ab d e
2 1 ab a q
3 10 cd m n
4 5 pd m n
最下面的也要去掉。不考虑‘CD’,'PD'不相同情况,可以把partition by B的B去了。
FROM (select T.*,
min(A) keep(DENSE_RANK first ORDER BY A) over(partition by B, GREATEST(C, D), LEAST(C, D)) AS A2
FROM T)
WHERE A = A2
ORDER BY A
如
A B C D
1 ab a q
2 ab a q
3 ab q a
4 cd c d