拾到的财富

Question

这是我在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;

Answer 1

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

Answer 2

11.随机函数也疯狂
Example:
sys_guid() 获取随机字符串。
dbms_random.value()获取随机数
select * from consume SAMPLE(10) where rownum <2;
SELECT * FROM (SELECT * FROM t ORDER BY sys_guid()) WHERE rownum < 2;这个的效率高些
select * from tb order by dbms_random.value() 12．这个选择性删除记录也很拽
Example:
create or replace trigger tt_del_tri 
  before delete on tb_Group when(old.groupName = 'system') 
declare 
  raise_application_error(-20000,'不能删除系统名字的列'); 
begin
阻止删除数据列
13.不老的Kill语句
一  如何查oracle的锁
 SELECT /*+ rule */ s.username,decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK',NULL)
 LOCK_LEVEL,o.owner,o.object_name,o.object_type,       
    s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
    FROM v$session s,v$lock l,dba_objects o
    WHERE l.sid = s.sid
    AND l.id1 = o.object_id(+)
    AND s.username is NOT NULL
二  如何杀掉锁的进程
    Alter system kill session 'sid,serial#'
    alter system disconnect session 'sid,serial#' immediate;
    在win上，还可以采用oracle提供的orakill杀掉一个线程（其实就是一个Oracle进程）
    在Linux/Unix上，可以直接利用kill杀掉数据库进程对应的OS进程
14．正则表达式也想小试牛刀
Example(regexp_substr,regexp_instr,regexp_like,regexp_replace):
SQL> select REGEXP_REPLACE('李四(AAA)','(.+)\((.+)','\1') from dual;REGEXP_REPLACE('李四(AAA)','(.
------------------------------
李四SQL> 
15. translate函数也来指招
Example(将a用w代替，b用e代替):
select translate('abcdef','ab','we') from dual;

Answer 3

select to_char(sysdate+rownum,'mm-dd') 
from dual connect by rownum <=7 这个确实“帝王之作”！

Answer 4

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) 
如何使用?有例子么

Answer 5

补一个：dense_rank():
例如有个table有个字段为PROJECT，根据PROJECT的升序，取出的数据为：
PROJECT
000A
000A
000A
000F
000F
000K
000P
000P由于要写个Insert into...select语句，插入对象table有两个字段是GROUPID,DETAILNO
这两个字段的取法为，根据PROJECT的不同，GROUPID从00001开始连续，在相同的PROJECT中，DETAILNO从1连续
结果如下：
PROJECT      GROUPID      DETAILNO
000A            00001          1
000A            00001          2
000A            00001          3
000F            00002          1
000F            00002          2
000K            00003          1
000P            00004          1
000P            00004          2想请问一下，这个能不能实现呢？答案：select project,
  to_char(dense_rank()over(order by project),'0000')groupid,
  row_number()over(partition by project order by rownum)detailno
from table1

Answer 6

有：
--Oracle merge into 的用法详解实例--  作用：merge into 解决用B表跟新A表数据，如果A表中没有，则把B表的数据插入A表；--  语法：--  MERGE INTO [your table-name] [rename your table here]--  USING ( [write your query here] )[rename your query-sql and using just like a table]--  ON ([conditional expression here] AND [...]...)--  WHEN MATHED THEN [here you can execute some update sql or something else ]--  WHEN NOT MATHED THEN [execute something else here ! ]-------实例-------drop table a;
drop table b;
create table a(id number(1),name varchar2(10));
create table b(id number(1),name varchar2(10));
insert into a values(1,'2');
insert into b values(1,'1');
insert into b values(2,'2');
commit;merge into  a
using (select id,name from b ) c
on(a.id=c.id )
when not matched then insert (a.id,a.name) values (c.id,c.name);
merge into  a
using (select id,name from b ) c
on(a.id=c.id )
when matched then update set a.name=c.name;merge into  a
using (select id,name from b ) c
on(a.id=c.id )
when matched then update set a.name=c.name
when not matched then insert (a.id,a.name) values (c.id,c.name);
作用：将表 b 数据 更新到 表a ，条件是a.id=b.id，如果a表中有该条件的数据就修改，没有该条件的数据就插入。如果你的数据量很大，此sql效率非常高。 

Answer 7

不错，可以整理到你的blog里呀。

Answer 8

小弟是菜鸟，弱弱的问下
请问下我想去买本有关SQL方面的书籍..大家有什么推荐的吗?

Answer 9

补充一个回帖：楼主，貌似你的
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;
有问题
V是一个查询出来的虚表，相当于一个视图功能，应该不能对其进行update吧
ORA-01779: 无法修改与非键值保存表对应的列