csdn经典语句收藏.doc

这是我在csdn上看到的一些自我感觉可以的语句, 小弟不敢独享,特拿来和大家共冕.如果有侵犯的地方 请大家包含 1. wm_concat函数也来打擂台 使用场景:简单的将一列的字段通过逗号连接起来。 Example: select a1,(wm_concat(b1),from aa group by a1 如果是10G版本以下通用的语句如下 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语句中实现字段的自动增长。) Example(实现了日期的自动增长): select to_char(sysdate+rownum,mm-dd) from dual connect by rownum =7 3.还是你最亲(csdn的兄弟们最喜欢用的虚表) Example: with tab as ( select 1 ? from dual union all select 2 from dual ) 4. 还真另类(将所有列使用||拼接后插入一个clob字段) insert into t_result(clob_col) select F_TASK_NAME||F_CREATER_NAME ||f_result from dual; 5．选择性插入语句，很实用 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; 6.这都被你给删了(删除数据重复的记录) delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc); 7．的确很美，嫁了吧(美妙的数据更新语句，前提是主键相同) update (select s.name name1,s2.name name2 from simple s,simple2 s2 where s.id=s2.id) v set v.name1=v.name2; 8．选择由你(针对同一数据表是选择插入，还是更新) 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) 9．Java爱好者（通用的oracle分页语句） SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM ( //生成标准的查询语句（没有分页条件的） Sql_statement ) row_ WHERE rownum =? pageSize * pageNumber ) WHERE rownum_ pageSize * (pageNumber - 1)) 10.让我也来个(使用频率高的查询连续记录) Example(查询连续3个月的记录): select id from (select id,sbrq,sbbz, add_months(trunc(sbrq, mm), ? 1 - rank() over(partition by id order by sbrq)) ord ? from (select id, sbrq, sbbz from gl_wsbxx where sbbz = N)) ? group by id, ord having count(*) = 3 11