解决方案 »
- oracle 动态调用存储过程,返回游标类型(重新提问)
- !!!!! 求 oracle11g RAC相关知识的中文资料^^^^^^^^^
- 请看这是个什么加密方式
- UNIX下ORACLE的网络应用开发
- 请问以下有关oracle的服务有什么作用,是针对什么程序而言的
- 天哪这是怎么回事
- 请问如何完全删除Oracle 8.0.6
- 在oracle中如何用sql语句来查询主键和外键
- [求助]XP下的FORM BUILDER 和REPORT BUILDER的安装问题!!!!
- 为什么会这样:DBMS_PIPE是没说明的呢?
- 请教oracle TIMESTAMP的使用
- DBLINK远程库表结构修改导致本地库调用此表的过程(主要是用过远程表同义词的行变量赋值)出现ORA-00932: 数据类型不一致: 应为 -, 但却获得 -
SQL> with t as(
2 select 1 no,'a' name,'abc' ad from dual union all
3 select 1,'a','cde' from dual union all
4 select 1,'b','abc' from dual union all
5 select 1,'c','abc' from dual union all
6 select 2,'b','cde' from dual union all
7 select 2,'d','cde' from dual)
8 ,b as(
9 select no,name,ad from (
10 select no,name,ad,
11 row_number() over (partition by no order by no,ad) rn
12 from t)
13 where rn=1)
14 ,c as(
15 select distinct t.name from t
16 where not exists(
17 select 1 from b
18 where t.name=b.name))
19 select * from b
20 union
21 select null,name,null from c
22 /
NO NAME AD
---------- ---- ---
1 a abc
2 b cde
c
d
with t as (
select 1 no, 'a' name, 'abc' addr from dual union all
select 1 no, 'a' name, 'cde' addr from dual union all
select 1 no, 'b' name, 'abc' addr from dual union all
select 1 no, 'c' name, 'abc' addr from dual union all
select 2 no, 'b' name, 'cde' addr from dual union all
select 2 no, 'd' name, 'cde' addr from dual),
a as (select rownum rn, t.* from t),
c as (select * from a where not exists
(select * from a b where b.no=a.no and b.rn<a.rn))
select no,name,addr from c union all
select null,name,null from t where t.name not in (select c.name from c);