关闭

oracle查询不重复数据

标签: oracleselect
2188人阅读 评论(0) 收藏 举报
分类:

方法一:

select jqbh,cjdbh,sjybh,sjyxm

from (select t.jqbh,t.cjdbh,t.sjybh,t.sjyxm,row_number() OVER(PARTITION BY t.sjybh ORDER BY t.jqbh) ds from T_ITMP_TCS_DISPOSAL t
where t.sjyxm is not null)
where ds=1

 方法二:
select jqbh,cjdbh,sjybh,sjyxm
from T_ITMP_TCS_DISPOSAL

where cjdbh in(select min(cjdbh) from T_ITMP_TCS_DISPOSAL WHERE sjyxm is not null group by sjybh);


结果集:


参考:

--去重查询方法一:根据id

select * from sxe where id in(select min(id) from sxe group by username) order by id asc;

--去重查询方法二:根据rownum

select * from (select s.*,rownum rn from sxe s ) x where x.rn in (select min(rownum) from sxe group by username) order by id asc;

--去重查询方法三:根据rowid

select * from (select s.*,rowid rid from sxe s) x where x.rid in (select min(rowid) from sxe group by username) order by id asc;

select s.*,rowid from sxe s where rowid in (select min(rowid) from sxe group by username) order by id asc;

 

--去重删除方法一:根据ID

delete from sxe where id not in (select min(id) from sxe group by username);

--去重删除方法二:根据rownum

--delete from (select s.*,rownum from sxe s) x where rownum not in (select min(rownum) from sxe group by username);

--去重删除方法三:根据rowid

delete from sxe where rowid not in (select min(rowid) from sxe group by username);


备注:补充

oracle 进行数据增删改查一定要进行事务处理,7个事务处理。若发现数据无法进行更新操作,那么一定是没有进行事务处理。则解决方法为解锁oracle,方法如下:
SELECT object_name, machine, s.sid, s.serial#
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid;

alter system kill session 'sid, serial#';
ALTER system kill session '180, 60715';


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:264005次
    • 积分:3333
    • 等级:
    • 排名:第10118名
    • 原创:106篇
    • 转载:5篇
    • 译文:0篇
    • 评论:34条
    文章分类
    最新评论