sql语句收集

1、wm_concat函数

SELECT t.id id, MAX(substr(sys_connect_by_path(t.sname, ','), 2)) str
  FROM (SELECT id,
               sname,
               row_number() over(PARTITION BY id ORDER BY sname) rn
          FROM a) t
 START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
       AND id = PRIOR id
 GROUP BY t.id;

 

2、sql语句中实现字段的自动增长。

select to_char(sysdate + rownum, 'mm-dd') from dual connect by rownum <= 7

3、临时表

with tab as
(
select 1
  from dual
union all
select 2 from dual
)

4、选择性插入语句，很实用

INSERT ALL WHEN type = 1 THEN INTO tab1
VALUES
  (myseq.NEXTVAL, val) WHEN type = 2 THEN INTO tab2
VALUES
  (myseq.NEXTVAL, val) WHEN type IN
  (3, 4, 5) THEN INTO tab3
VALUES
  (myseq.NEXTVAL, val) ELSE INTO tab4
VALUES
  (myseq.NEXTVAL, val)
  SELECT type, val FROM source_tab;

5、针对同一数据表是选择插入，还是更新

merge into a
using b
on (a.USER_NO = b.USER_NO)
when matched then
  update set a.NAME = b.NAME
when not matched then
  insert values (b.USER_NO, b.NAME)

5、oracle分页

SELECT *
  FROM (SELECT row_.*, rownum rownum_
          FROM (/ / 生成标准的查询语句（没有分页条件的） Sql_statement) row_
         WHERE rownum <= pageSize * pageNumber
       
        )
 WHERE rownum_ > pageSize * (pageNumber - 1))