oracle常用查询

查询某一列中重复数据
select username from xtgl_superusers group by username having count(username) >1

更新符合条件的人在后面加‘del’
update xtgl_superusers set username= CONCAT(username, 'del') where islock=1

ORACLE 如何按逗号截取查询所得的表名
SELECT   substr(T1.COMMENTS ,0,instr(T1.COMMENTS ,',',-1,1)-1)  as 列名  from  USER_TAB_COMMENTS  T1

select * from t where instr(','||sd_dept||',', ','||'一场'||',')>0 就是判断 是否包含一场的sql

 

截取字符串
update xtgl_superusers s set s.username=substr(s.username,1,instr(s.username ,'del',1,1)-1) where username like '%del%' and islock=0

根据pid查询循环一层一层的数据
select id from wggl_depart START WITH PID = 22825 CONNECT BY PRIOR ID = PID

更改所有aaa为bbb
update personnel t set t.url=replace(url,'aaa','bbb') where t.url is not null

查询在线人员列表
select * from superusers su where su.islock=0 and (su.onlinetime>sysdate-interval '65'second)

length使用
select id from depart d where length(d.sortall)=21 and d.islocf = 1 and d.state =1