Oracle 常用SQL写法

1、取表中某个字段，包含某字符串的数据

Select h.*
  From Table_Name h
 Where h.Interface_Code = 'XXXXXXXX'
   and 1 = 1
   And h.Creation_Date >= Sysdate - 1
   And instr(substr(h.Request_Data, 1, 3500), 'XXXXX0001') > 1

2、查询某个字段是否有重复数据

select COUNT(1), T.SEGMENT2
  from Table_Name T
 WHERE 1 = 1 HAVING COUNT(1) > 1
 GROUP BY T.SEGMENT2

 

3、取表中同一标识的多条数据中的最新数据

select a.header_id
  from (SELECT ROW_NUMBER() OVER(PARTITION BY t.PROJECT_ID ORDER BY t.last_update_date DESC) rn,
               t.header_id
          FROM Table_Name t
         WHERE 1 = 1
           AND T.Project_Id = 1) a
 where a.rn = 1

4、字符串按分号隔开，用正则表达式拆成多列

SELECT REGEXP_SUBSTR('A;B;C;D;E;AA', '[^;]+', 1, ROWNUM)
  FROM DUAL
CONNECT BY ROWNUM <= LENGTH('A;B;C;D;E;AA') -
           LENGTH(REPLACE('A;B;C;D;E;AA', ';', '')) + 1

SELECT *
  FROM Table_Name t
  From t.segment1 IN
       (SELECT REGEXP_SUBSTR('A;B;C;D;E;AA', '[^;]+', 1, ROWNUM)
          FROM DUAL
        CONNECT BY ROWNUM <= LENGTH('A;B;C;D;E;AA') -
                   LENGTH(REPLACE('A;B;C;D;E;AA', ';', '')) + 1)

5、（行列）多行数据将某个字段合并成一行

SELECT listagg(t.sql_text, ',') within GROUP(ORDER BY t.piece) AS NAME
  FROM (SELECT st.sql_text, piece
          FROM apps.fnd_concurrent_requests req,
               gv$session                   gs,
               gv$sqltext                   st
         WHERE 1 = 1
           AND req.request_id = 25166889
           AND req.oracle_session_id = gs.audsid
           AND gs.sql_id = st.sql_id
         ORDER BY piece) t
 GROUP BY t.piece;

6、（列转行）多个字段数据展示成不同行数据

正常查我们的sql和结果长这样：

 

如果需要将行转成列，sql如下：

SELECT T1.*
  FROM (select v.lookup_code || v.SEGMENT1 SEGMENT1,
               v.lookup_code || v.SEGMENT2 SEGMENT2,
               v.lookup_code || v.SEGMENT3 SEGMENT3,
               v.lookup_code || v.SEGMENT4 SEGMENT4,
               v.lookup_code || v.SEGMENT5 SEGMENT5
          from cux_fnd_lookup_values v
         where v.lookup_type = 'CUX_EAM_ASSET_CATEGORY_GROUP'
           and v.enabled_flag = 'Y'
           and v.lookup_code = '21'
           and nvl(v.start_date_active, sysdate) <= sysdate
           and nvl(v.end_date_active, sysdate + 1) >= sysdate) unpivot(CATEGORY_CODE FOR b IN(SEGMENT1,
                                                                                              SEGMENT2,
                                                                                              SEGMENT3,
                                                                                              SEGMENT4,
                                                                                              SEGMENT5)) T1

 结果如下：