table t:name
----
sql_name
sql_name
str_name
str_name要求结果为:
table t:name
----
sql_name
str_name中间不能用到create 其它table等命令。。大侠求救呀
----
sql_name
sql_name
str_name
str_name要求结果为:
table t:name
----
sql_name
str_name中间不能用到create 其它table等命令。。大侠求救呀
解决方案 »
- 初学者求指导,导入DMP表在哪查看?
- 求解数据的倒入问题,请高手指教,thank you!
- 急求高手解决一个查询语句!
- 求触发器,有点难度
- 处理大字段时遇到问题:UTL_RAW.CAST_TO_RAW 提示 ora-06553: pls-306 "wrong number or types of arguments in call to ,请高人指点1,
- Oracle数据库安装的时候需要注意什么.?
- SQL> delete from a where exists (select 'X' from b where a.bm=b.bm and a.mc=b.mc);
- 约束条件的问题!
- 用sqlldr装载数据不方便, 无法控制装载过程,想自己写一个类似的装载工具, 请问oracle提供了这样的c语言接口没?
- 是不是ORA有问题哟
- 我在某人的博客中看见的,以下这句话对吗?
- 大家帮我推测一下是什么原因让我的Oracle变得超慢极其慢
这样吧,给你也例子。对你有很多帮助!--查询和删除相同记录的测试
create table test(id number, name varchar2(20));
insert into test(id, name) values(1,'10');
insert into test(id, name) values(1,'10');
insert into test(id, name) values(2,'20');
insert into test(id, name) values(2,'30');
insert into test(id, name) values(3,'40');
insert into test(id, name) values(3,'40');
commit;
select * from test;--查询相同记录
select id,name from(select id,name,count(*) from test group by id,name having count(*)>1);
select id,name from test a where rowid > (select min(rowid) from test b where a.id = b.id and a.name = b.name);
select id,name from test a where rowid <> (select max(rowid) from test b where a.id = b.id and a.name = b.name);--查询不同记录
1.select * from test t where t.rowid <= (select min(x.rowid) from test x where t.id = x.id and t.name = x.name);
2.select distinct t.* from test t;删除重复记录
1.delete from test t where t.rowid > (select min(x.rowid) from test x where t.id = x.id and t.name = x.name);
2.delete from test t where t.rowid <> (select min(x.rowid) from test x where t.id = x.id and t.name = x.name);
3.delete from test
where rowid in(select rd
from(select rowid rd,row_number()over(partition by id,name order by 1 )rn
from test
)
where rn <>1
);
1.delete from t where t.rowid > (select min(x.rowid) from t x where t.name = x.name);
2.delete from t where t.rowid <> (select min(x.rowid) from t x where t.name = x.name);
3.delete from t
where rowid in(select rd
from(select rowid rd,row_number()over(partition by name order by 1 )rn
from t
)
where rn <>1
);