一些常见sql语句整理

一些常见sql语句整理
很早以前就在edaily里收藏了一些sql语句,都很简单.不过看到itpub里大家总是翻来覆去的问相同的问题.今天稍微做一个整理.发到blog上.[@more@]

1,删除重复记录.

delete from emp e1 where e1.rowid!=(select max(rowid) from emp e2
where e1.empno=e2.empno and e1.ename=e2.ename);

delete from emp e1 where rowid>any(select rowid from emp e2 where e1.empno=e2.empno);

----同样逻辑也可以如此.很简单.不多说了.

2,怎么用SQL随机取记录？
在Oracle中，例如有一个员工表，我想随机从中取出指定X个人，以便让这X个员工成为“幸运”员工，能用一个SQL实现吗？

这样就可以
select * from (select * from xxx order by dbms_random.random) where rownum < 5;

select * from (select * from xxx order by sys_guid()) where rownum < 5;

3,求个函数是用来判断一个字符串是数字.

select decode(ltrim('9797979765464','0123456789'),null,'Yes','No') Is_Number from dual;

----其实这里也可用translate() 函数

4,sysdate怎么取n个小时前时间？

select sysdate,sysdate-1 one_date,sysdate-1/24 one_hour,sysdate-1/(60*24) one_minute,sysdate-1/(60*24*60) one_second
from dual
----这个里面日，时，分，秒都有的，自己需要什么时间把1改正你要的就可以了

5,'='号条件有没有代表全部的？ 语句里面想实现 如果part='test' 就设t1.name=t2.name的条件否则不进行name条件过滤即 part=什么的时候代表全部数据?

select *
from table1 t1,talbe2 t2
where t1.name=decode(part,'test',t2.name,???)

select *
from table1 t1,talbe2 t2
where nvl(t1.name,'1')=decode(part,'test',nvl(t2.name,''),nvl(t1.name,'1'))

select *
from table1 t1,talbe2 t2
where t1.name like decode(part,'test',t2.name,‘%’)

----实际上就是decode()函数简单的用法.

6,分组行列变为列语句.比如scott.emp得出
DEPTNO ENAME
---------- -------------------------------------
10 CLARK KING MILLER
20 FORD JONES SCOTT SMITH
30 ALLEN BLAKE JAMES MARTIN TURNER WARD
语句
select distinct
first_value(deptno) over(partition by deptno order by lv desc) as deptno,
first_value(col3) over(partition by deptno order by lv desc) as ename
from (
select deptno,sys_connect_by_path(ename,' ') col3,level lv
from (
select deptno,ename,lag(ename,1,null) over (partition by deptno order by ename) ename_1 from emp)
connect by prior ename=ename_1
)
/

----这是我以前写的.其实效率挺低下的.有很多替代方法.下面两个例子体现了该类问题的另一种思路

SELECT substr(Max(sys_connect_by_path(tmp,'|')),2) AS result FROM (
SELECT row_Number() over ( ORDER BY p) AS r2,tmp FROM(
SELECT SubStr(Max(sys_connect_by_path(len,',')),2) AS tmp, p FROM (
SELECT Row_Number() over (PARTITION BY SubStr(pos,0,1) ORDER BY SubStr(pos,1,1)) AS r1 ,
SubStr(pos,0,1) p ,len
FROM test_123
)
start WITH r1=1
CONNECT BY PRIOR p = p
AND PRIOR r1 = r1 - 1
GROUP BY p
)
)
start WITH r2=1
CONNECT BY PRIOR r2 = r2 - 1

ID OBJ NAME1 NAME2
1 101 a b
2 101 c d
3 101 e f
5 102 3 4
4 102 1 2

求助一条简单SQL（急）
有个表id,price,type，type是钱的类型，现在要查所有钱的类型的总和，结果如：车费：餐费：怎么写呢 （t要求只显示一条记录，也就是钱的类型要横向显示）
SQL> select substr(result,2) result from(
2 select sys_connect_by_path(col,',') as result,level lv
3 from(
4 select rownum rn,col,level from(
5 select deptno||': '||sum(sal) col from emp group by deptno)
6 )
7 connect by prior rn=rn-1
8 order by lv desc) where rownum=1
9 /

RESULT
----------------------------------------------------------------
10: 8750,20: 6775,30: 9400

7,求某一列的写法(行转列的一种思路)
SQL> select all_name from(
2 select sys_connect_by_path(ename,' ') all_name,level
3 from (
4 select ename,lag(ename,1,null) over (order by empno) em from emp
5 ) connect by prior ename=em
6 order by level desc)
7 where rownum<=1
8 /

ALL_NAME
-------------------------------------------------------------------------
SMITH ALLEN WARD JONES MARTIN BLAKE CLARK KING TURNER JAMES FORD MILLER

----昨天在pub里回复了个相似的问题,也是使用了lag()/lead()函数大概如下,主要应用需要比较某列上下行数据的时候.
sql问题，请帮帮忙？
在表empay表中有字段a,b,c 代表卡号,日期,金额，现想提取卡号连续3天以上消费100元以上的数据，该怎么提？

create table empay(a varchar2(10),b varchar2(10),c number);
insert into empay values('1','20070101',100);
insert into empay values('1','20070102',200);
insert into empay values('1','20070103',300);
insert into empay values('2','20070103',300);
insert into empay values('2','20070101',90);
insert into empay values('3','20070101',190);
insert into empay values('4','20070103',390);
commit;

SQL> select a,b from(
2 select a,b ,lag(b,1,null) over (partition by a order by b) b2,lag(b,2,null) over (partition by a order by b) b3
3 ,c,lag(c,1,null) over (partition by a order by b) c2,lag(c,2,null) over (partition by a order by b) c3 from EMPAY a
4 ) where b-b2<=1
5 and b2-b3<=1
6 and c>=100
7 and c2>=100
8 and c3>=100
9 /

A B
---------- ----------
1 20070103

----该问题还有另一种比较好的思路.

create table a_tst(a varchar2(10),b date,c number);
insert into a_tst values('1',to_date('20070101','yyyymmdd'),100);
insert into a_tst values('1',to_date('20070102','yyyymmdd'),200);
insert into a_tst values('1',to_date('20070103','yyyymmdd'),300);
insert into a_tst values('1',to_date('20070104','yyyymmdd'),301);
insert into a_tst values('2',to_date('20070103','yyyymmdd'),300);
insert into a_tst values('2',to_date('20070101','yyyymmdd'),90);
insert into a_tst values('2',to_date('20070102','yyyymmdd'),130);
insert into a_tst values('3',to_date('20070101','yyyymmdd'),190);
insert into a_tst values('3',to_date('20070103','yyyymmdd'),125);
insert into a_tst values('4',to_date('20070103','yyyymmdd'),390);
commit;

select distinct a from
(select a,count(pos) p from
(select a,b,c,t,b-to_date('2007-01-01','yyyy-mm-dd')-t pos
from
(select a,b,c,rank() over (partition by a order by b) t
from a_tst where c>=100))
group by a,pos ) where p>=3

-----下面是该类问题的令一个例子:
问：如何求出有那些记录号码在3天内重复录入次数超过或等于2次。

利用分析函数可如下写。其中如果
select distinct rec_no from (
select rec_no, input_time - lead(input_time) over (partition by rec_no order by input_time DESC) as time_interval
FROM TABLE)
where time_interval>=2

8,从字符串中取数组（思路非常的妙）

var str varchar2(30)
exec :str := 'abc123def321a1'

select max(replace(sys_connect_by_path(ch, '/'), '/')) num
from (
select ch, rn - min(rn) over(partition by rn - rownum) rn, rn - rownum grp
from (
select substr(:str, rownum, 1) ch, rownum rn
from dual connect by rownum <= length(:str)
) where ch between '0' and '9'
) start with rn = 0
connect by rn - 1 = prior rn and grp = prior grp
group by grp
order by grp
/

NUM
---------------------------------------------------------------------------------------

123
321
1

9,按照某一定时段(5分钟)进行统计
表结构：
表名称：CALLDATA
ID INT， ID
BEGINTIME TIME， 时间
CALL CHAR（50） 用户
现在的语句是每分钟查询一次
select to_char(begintime,'yyyymmddhh24mi') as time,count(*) from calldata where begintime>=to_date('200512281000','yyyymmddhh24mi') and begintime

按照每五分钟统计
group by to_char(begintime,'yyyymmddhh24mi'); 改为
group by to_char(begintime,'yyyymmddhh24'),ceil(to_char(begintime,'mi')/5)

SELECT A.PER5,NVL(B.CNT,0) AS CNT
FROM (SELECT ROWNUM-1 AS PER5 FROM ALL_OBJECTS WHERE ROWNUM<=12) A
LEFT OUTER JOIN (
SELECT TRUNC((DT-TO_DATE('20051229 20','YYYYMMDD HH24'))*1440/5) AS PER5,COUNT(*) AS CNT
FROM A
WHERE DT>=TO_DATE('20051229 20','YYYYMMDD HH24') AND DT<=TO_DATE('20051229 21','YYYYMMDD HH24')
GROUP BY TRUNC((DT-TO_DATE('20051229 20','YYYYMMDD HH24'))*1440/5)
) B
ON A.PER5=B.PER5
ORDER BY 1
/

10,请教：SQL的分列汇总语句
表如下：
name lx hm
张一 gh 123
张一 sj 123
张一 vod 123
李二 gh 123
李二 vod 225
李二 sj 338
说明：name:员工姓名 lx :业务类型 hm:号码
现在要按业务类型进行统计，列出如下：
name gh sj vod total
张一 1 1 1 3
李二 1 1 1 3

select name,
sum(decode(lx,gh,1,0)) as gh,
sum(decode(lx,sj,1,0)) as sj,
sum(decode(lx,void,1,0)) as void,
count(*) as total
from table group by name;

----decode()函数很多时候和 case when 是通用的. 如下语句

SQL> select deptno,sum(case when sal<2000 then 1 else 0 end) poor,
2 sum(case when sal>=2000 then 1 else 0 end) wealth,
3 count(*) from emp
4 group by deptno;

DEPTNO POOR WEALTH COUNT(*)
---------- ---------- ---------- ----------
10 1 2 3
20 1 2 3
30 3 3 6

以上是我从以前整理的语句摘抄一部分有代表性的情况.以后遇到好的语句后,会继续更新该帖子.在我们写统计语句的时候很多时候会用到功能强大的分析函数.这里我想将分析函数单独再开一帖子进行积累.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/167606/viewspace-906158/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/167606/viewspace-906158/