Oracle常用语句记录

交集/差集/合集  
    select * from tb_a  
        intersect  
        minus  
        union all  
    select * from tb_b  
      
条件分支 decode()  
    例如：搜索条件没有手机就查座机 默认查手机  
    SELECT * FROM   
    AND DECODE(SEND_MOBILE,NULL,TELNUM,SEND_MOBILE) LIKE '110'  
      
行号输出  
    row_number() OVER(ORDER BY null)  
    或者 rownum伪列  
      
聚合输出  
    SELECT LISTAGG(STUNAME,'+') WITHIN GROUP (ORDER BY STUNUM) OVER (PARTITION BY CLASSNAME)  
    FROM STU_INFO  
      
同时进行INSERT+UPDATE 即：如果某数据已存在于表1中则UPDATE表1，否则INSERT  
    MERGE INTO TBL1 T1  
    USING (SELECT 'D0001' ID,'KING' NAME FROM DUAL) T2  
    ON (T1.ID = T2.ID)  
    WHEN MATCHED THEN  
      UPDATE SET NAME = T2.NAME  
    WHEN NOT MATCHED THEN  
      INSERT VALUES(T2.ID,T2.NAME)  
  
与上一条数据一并输出  
    SELECT '轮到'||STU_NAME,'下一个是'||LAG(STU_NAME,1) IGNORE NULLS OVER(ORDER BY STUNUM) FROM STUINFO