一些懒得重写的sql

1、NOT EXISTS

UPDATE TABLE1 SET colum5='00'
WHERE NOT EXISTS (
        SELECT column1,column2 FROM TABLE2 
        WHERE TABLE1.column1= TABLE2.column1AND TABLE1.column2= TABLE2.column2
)AND column3 = '11'

2、某字段多条记录

select * from TABLE1 where
column1 in (
select column1 from TABLE1 where column2='00' group by column1 having count(column1)>1) and
rowid not in (select min(rowid) from TABLE1 column2='00' group by column1 having count(column1)>1)

3、重复数据

select column1 from TABLE1 where column1='00' group by column1 having count(column1)>1 

4、空格

select * from TABLE1 where column1 like '% %'

5、数据统计及行列转换

select max(ts.column4),td.column1,max(td.column2),
max(decode(ts.column3,'1',ts.taskNum,'0')) ,
max(decode(ts.column3,'2',ts.taskNum,'0')) ,
max(decode(ts.column3,'3',ts.taskNum,'0')) ,
max(decode(ts.column3,'4',ts.taskNum,'0')) ,
max(decode(ts.column3,'5',ts.taskNum,'0')) ,
max(decode(ts.column3,'6',ts.taskNum,'0'))
from TABLE1  td join 
	(SELECT column1,column3,count(TASK_ID) as Num, 
	(SELECT count(1) from TABLE1) as column4 
		FROM TABLE1 
		group by column3,column1) ts 
	on td.column1 = ts.column1 
group by td.column1,td.column2 

6、数据统计

select max(a),max(b),max(c),max(d),max(e),max(f) from 
	(select 
	case when column1='1' then count(column2) else 0 end as a, 
	case when column1='2' then count(column2) else 0 end as b, 
	case when column1='3' then count(column2) else 0 end as c, 
	case when column1='4' then count(column2) else 0 end as d, 
	case when column1='5' then count(column2) else 0 end as e,  
	case when column1='6' then count(column2) else 0 end as f  
	from TABLE1 
	group by  column1) 

7、树状显示数据

select a.seq,
       a.menu_code,
       a.lv,
       a.menu_title,
       a.parent_code,
       pm.menu_title,
       decode(a.menu_type, 'M', '菜单', 'T', '交易') as menu_type,
       a.tran_code
  from (select rownum seq,
               lpad(' ', 2 * (level - 1), ' ') || t.menu_code as menu_code,
               level lv,
               t.menu_title,
               t.parent_code,
               t.menu_type,
               t.tran_code
          from ets_appmenu t
         where t.menu_status = 'V'
         start with t.parent_code = '0000'
        connect by prior t.menu_code = t.parent_code) a
  left join (select *
               from ets_appmenu b
              where regexp_like(b.menu_code, '([A-Z])')
                and b.menu_status = 'V') pm
    on a.parent_code = pm.menu_code;

8、检查锁表

SELECT al.owner "所有者",                                  
       al.object_name "表名",                              
       al.object_type "表类型",                            
       lo.session_id "会话id",                             
       s.osuser "登录者机器的用户",                        
       s.machine "登录者机器名",                           
       l.ctime "被锁时间",                                 
       l.BLOCK "=1的是制造者",                             
       l.TYPE "锁类型",                                    
       p.spid "系统进程号",                                
       s.serial#                                           
  FROM v$locked_object lo                                  
  JOIN all_objects al ON al.object_id = lo.object_id       
  JOIN v$session s ON s.sid = lo.session_id                
  JOIN v$lock l ON l.sid = lo.session_id                   
  JOIN v$process p ON p.addr = s.paddr;                    
                                                           
ALTER system kill session '2557,60703';                    
ALTER system disconnect session '会话id,SERIAL#' IMMEDIATE;          

9、检查锁进程

SELECT b.sid,                                              
       b.serial#,                                          
       b.machine,                                          
       b.terminal,                                         
       b.program,                                          
       b.process,                                          
       b.status                                            
  FROM v$lock a, v$session b                               
 WHERE a.sid = b.sid                                       
   AND b.machine = 'WORKGROUP\KJC-36-PC';                  
                                                           
SELECT b.machine, b.terminal, b.serial#, a.*               
  FROM v$session_wait a, v$session b                       
 WHERE a.sid = b.sid                                       
   AND b.terminal = 'KJC-36-PC';                           
                                                           
SELECT *                                                   
  FROM dba_ddl_locks t                                     
 WHERE t.owner = 'C50HST'                                  
   AND t.NAME = upper('ap_gl_balance_collating_01');       
                                                           
SELECT *                                                   
  FROM v$process p, v$session s                            
 WHERE p.addr = s.paddr                                    
   AND s.username = 'C50HST'                               
   AND nvl(s.osuser, 'x') <> 'SYSTEM'                      
   AND s.TYPE <> 'BACKGROUND'                              
   AND s.osuser = 'kjc-36';                  

10、按某字段分组，修改每组四分之一的数据

select A.* from
        (select a,b,c, rank()over(partition by d order by a) as nu 
        from EXT_BRANINFO where ...) A
join  EXT_LEGAL b
on a.legal_code=b.legal_code
where  mod(a.nu,4)=0  
--mod(a.nu,4)=1
--mod(a.nu,4)=2
--mod(a.nu,4)=3

11、关联修改

UPDATE
(
    SELECT
        a.column1, a.column2,a.column3,
        b.column5
    FROM
        table a
    INNER JOIN 
        (select  column1,column5 from table where ...) b
    ON 
        a.column1= b.column1
)
SET column2= column5, column3='123'

 

转载于:https://my.oschina.net/fjr/blog/735692