sql语句收集
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.随机函数也疯狂
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;
使用场景:简单的将一列的字段通过逗号连接起来。
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.随机函数也疯狂
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;
