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列)
解决方案 »
- oracle表的更新时间。
- constraints
- 数据库数据表现形式转换
- 藏拙中文分词系统DLL版出台了,并提供C,java,C#,delphi各种语言的DEMO和开发源码!兄弟们大开眼界的机会到了,所有程序完全由本人独自一人所开发!
- 用存储过程动态创建触发器来记录表数据的更新,巨头疼的问题,高分求解!!!
- oracle数组的问题,请高手帮助解决,本人不甚感激之
- 关于字符集,字符集改变后原来插入的汉字显示乱码,新插入的正常
- 关于日期时间格式字符串的问题?
- Oracle视图中如何取出记录集中最后一条记录(急!)
- 这条SQL该怎么写?
- 求一个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