1、計算不包含最大值和最小值的均值
2、把字母數字串轉換為數值
3、更改累計和中的值–顯示存款或取款後的值
4、加減日、月、年
5、計算兩個日期之間的天數
6、確定兩個日期之間的工作日數目
表EMP中，計算BLAKE和JONES的hiredate（聘用日期）之間的工作日數（除去星期六、星期天）
7、確定兩個日期之間的月份數或年數
例如：EMP表中，求第一個員工和最後一個員工之間相差的月份數，以及這些月摺合的年數
8、確定兩個日期之間的秒、分、小時數
例如：EMP表中，求ALLEN和WARD的hiredate（聘用日期）之間相差的時間，分別用秒、分、小時表示
9、計算一年中周內各日期的次數
10、確定當前記錄和下一條記錄之間相差的天數
11、確定一年是否為閏年
12、確定一年內的天數
13、從日期中提取時間的各個部分
14、確定某個月的第一天和最後一天
15、列出當年中的所有所於星期五的日期
16、確定某月內第一個和最後一個“周內某天”的日期
例如：找出當前月的第一個星期一及最後一個星期一的日期 next_day下一個星期幾
17、列出一年中每個季度的開始日期和結束日期
18、確定某個給定季度的開始日期和結束日期
19、填充丟失的日期
–為給定範圍內的每個日期(每個月、周或年)生成一行資訊，這樣的行集通常用於生成綜合報告。
–例如，計算每年內每個月聘用的員工數。檢查已聘用的所有員工的聘用日期，其範圍是 1980-1983
–現在，要確定1980-1983年間每個月聘用的員工數。如有哪個月沒有聘用，則顯示為0個
20、按照給定的時間單位進行查詢
–問題:查詢與給定月份、星期幾或其他時間單位相匹配的日期。
–例如：找到2月份和12月份聘用的所有員工或者查詢星期二聘用的所有員工
21、使用日期的特殊部分比較記錄
–問題：查詢聘用日期月份和周內日期都相同的員工。例如，如果在1988年3月10日星期一聘用了某了員工，
–而在2001年3月2日星期一聘用了另一個員工，那麼，由於二者的聘用日期都在星期一，而且月份名一致，則可以認為他們相匹配
22、識別重疊的日期範圍
–問題：查詢員工在老工程結束之前就開始新工作的所有例項 當結束那天正好接了另一個專案也算
–一個開始時間在 另一行記錄的開始時間和結束時間之間
1、計算不包含最大值和最小值的均值
SELECT (SUM(ID)-MAX(ID)-MIN(ID))/(COUNT(1)-2) FROM tb_dict
2、把字母數字串轉換為數值
SELECT REPLACE(TRANSLATE(LOWER('adc12s3as'),
LOWER('abcdefghijklmnopqrstuvwxyz'),
RPAD('z', 24, 'z')),
'z',
'')
FROM DUAL
3、更改累計和中的值–顯示存款或取款後的值
with tmp as (
select 1 tid,100 atm,'存款' trx from dual union all
select 2 tid,100 atm,'存款' trx from dual union all
select 3 tid,50 atm,'取款' trx from dual union all
select 4 tid,100 atm,'存款' trx from dual union all
select 5 tid,200 atm,'取款' trx from dual union all
select 6 tid,50 atm,'取款' trx from dual
)
select trx,atm,sum(decode(trx,'存款',atm,-atm)) over (order by tid) balance from tmp
4、加減日、月、年
SELECT HIREDATE,
HIREDATE – 5 AS HD_MINUS_5D,–減日
HIREDATE + 5 AS HD_PLUS_5D,–加日
ADD_MONTHS(HIREDATE, -5) AS HD_MINUS_5M,–減月
ADD_MONTHS(HIREDATE, 5) AS HD_PLUS_5M,–加月
ADD_MONTHS(HIREDATE, -5 * 12) AS HD_MINUS_5Y,–減年
ADD_MONTHS(HIREDATE, 5 * 12) AS HD_PLUS_5Y–加年
FROM EMP
WHERE DEPTNO = 10
5、計算兩個日期之間的天數
SELECT to_date('2012-10-22', 'yyyy-mm-dd') – to_date('2012-9-22', 'yyyy-mm-dd') FROM dual;
6、確定兩個日期之間的工作日數目
表EMP中，計算BLAKE和JONES的hiredate（聘用日期）之間的工作日數（除去星期六、星期天）
步驟：
1)、建立索引表
create table t500(
id number(3)
)
2)、插入索引資料
declare
v_index number(3);
begin
for v_index in 1..500 loop
insert into t500 values(v_index);
end loop;
end;
3)、查詢語句ss
SELECT SUM(CASE
WHEN TO_CHAR(JONES_HIREDATE + T500.ID – 1, 'DY') IN('星期六', '星期日') THEN 0 ELSE 1
END) AS DAYS
FROM (SELECT MAX(CASE WHEN ENAME = 'BLAKE' THEN HIREDATE END) AS BLAKE_HIREDATE,
MAX(CASE WHEN ENAME = 'JONES' THEN HIREDATE END) AS JONES_HIREDATE
FROM EMP
WHERE ENAME IN ('BLAKE', 'JONES')) X,
T500
WHERE T500.ID <= BLAKE_HIREDATE – JONES_HIREDATE + 1
7、確定兩個日期之間的月份數或年數
例如：EMP表中，求第一個員工和最後一個員工之間相差的月份數，以及這些月摺合的年數
SELECT MONTHS_BETWEEN(MAX_HIREDATE, MIN_HIREDATE),
MONTHS_BETWEEN(MAX_HIREDATE, MIN_HIREDATE) / 12
FROM (SELECT MIN(HIREDATE) MIN_HIREDATE, MAX(HIREDATE) MAX_HIREDATE
FROM EMP)
8、確定兩個日期之間的秒、分、小時數
例如：EMP表中，求ALLEN和WARD的hiredate（聘用日期）之間相差的時間，分別用秒、分、小時表示
select dy*24 as hr, dy*24*60 as min, dy*24*60*60 as sec
from (select (max(case when ename='WARD' then hiredate end)-max(case when ename='ALLEN' then hiredate end)) as dy from emp)
9、計算一年中周內各日期的次數
SELECT TO_CHAR(TRUNC(SYSDATE, 'y') + ROWNUM – 1, 'DY'), COUNT(*)
FROM T500
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE, 'y'), 12) – TRUNC(SYSDATE, 'y')
GROUP BY TO_CHAR(TRUNC(SYSDATE, 'y') + ROWNUM – 1, 'DY')
10、確定當前記錄和下一條記錄之間相差的天數
SELECT ENAME, HIREDATE, NEXT_HD, NEXT_HD – HIREDATE DIFF
FROM (SELECT DEPTNO,
ENAME,
HIREDATE,
LEAD(HIREDATE) OVER(ORDER BY HIREDATE) NEXT_HD
FROM EMP)
WHERE DEPTNO = 10
11、確定一年是否為閏年
此處採用了最簡單的方案，檢查2月最後一天，如果是29，則當年就為閏年，即此處的關鍵是得到2月的最後一天
select to_char(last_day(add_months(trunc(sysdate, 'y'), 1)),'DD') from dual
12、確定一年內的天數
select add_months(trunc(sysdate, 'y'), 12)-trunc(sysdate, 'y') from dual
13、從日期中提取時間的各個部分
SELECT TO_CHAR(SYSDATE, 'hh24') HOUR,
TO_CHAR(SYSDATE, 'mi') MIN,
TO_CHAR(SYSDATE, 'ss') SEC,
TO_CHAR(SYSDATE, 'dd') DAY,
TO_CHAR(SYSDATE, 'mm') MONTH,
TO_CHAR(SYSDATE, 'yyyy') YEAR
FROM DUAL
14、確定某個月的第一天和最後一天
SELECT TRUNC(SYSDATE, 'mm') FIRSTDAY, LAST_DAY(SYSDATE) LASTDAY FROM DUAL
15、列出當年中的所有所於星期五的日期
with tmp_a as(
–獲取每一天
select level num,trunc(sysdate,'y')+level-1 everyday from dual
connect by level <= add_months(trunc(sysdate,'yyyy'),12)-trunc(sysdate,'y'))
select num,everyday,to_char(everyday,'day')
from tmp_a
where to_char(everyday,'day') = '星期五'
16、確定某月內第一個和最後一個“周內某天”的日期
例如：找出當前月的第一個星期一及最後一個星期一的日期 next_day下一個星期幾
–方法一
with tmp_a as (
select level num,trunc(sysdate,'mm')+level-1 everyday from dual
connect by level <= last_day(sysdate)-trunc(sysdate,'mm')+1)
select min(everyday) 第一個星期一,
max(everyday) 最後一個星期一 from tmp_a
where to_char(everyday,'day') = '星期一'
–方法二
select next_day(trunc(sysdate,'mm')-1,'星期一'),
next_day(last_day(trunc(sysdate,'mm')),'星期一')-7
from dual
17、列出一年中每個季度的開始日期和結束日期
with tmp_a as(
select ddate,to_char(ddate,'q') jidu from (
select level,trunc(sysdate,'y')+level-1 ddate from dual
connect by level <= add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y'))
)select jidu 季度,min(ddate) 開始日期,max(ddate) 結束日期 from tmp_a
group by jidu
18、確定某個給定季度的開始日期和結束日期
–方法一
with tmp_a as(
select ddate,to_char(ddate,'q') jidu from (
select level,trunc(sysdate,'y')+level-1 ddate from dual
connect by level <= add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y'))
)
select min(ddate),max(ddate) from tmp_a
where jidu = '4'
–方法二
select jidu,add_months(ddate,-2) 開始日期,last_day(ddate) 結束日期 from (
select jidu,to_date(ye||lpad(mon,2,'0'),'yyyymm') ddate from (
select jidu,substr(jidu,1,4) ye,mod(jidu,10)*3 mon from (
select 20081 jidu from dual union all
select 20082 from dual union all
select 20083 from dual union all
select 20084 from dual
)))
19、填充丟失的日期
–為給定範圍內的每個日期(每個月、周或年)生成一行資訊，這樣的行集通常用於生成綜合報告。
–例如，計算每年內每個月聘用的員工數。檢查已聘用的所有員工的聘用日期，其範圍是 1980-1983
–現在，要確定1980-1983年間每個月聘用的員工數。如有哪個月沒有聘用，則顯示為0個
with tmp_a as(
select 'aa' tno,to_date('19800506','yyyymmdd') hiredate from dual union all
select 'bb' tno,to_date('19830701','yyyymmdd') hiredate from dual union all
select 'cc' tno,to_date('19860702','yyyymmdd') hiredate from dual union all
select 'dd' tno,to_date('19810510','yyyymmdd') hiredate from dual union all
select 'ee' tno,to_date('19820503','yyyymmdd') hiredate from dual union all
select 'gg' tno,to_date('19820504','yyyymmdd') hiredate from dual union all
select 'ff' tno,to_date('19821015','yyyymmdd') hiredate from dual
)
select sta,sum(cnt) cnts,count(hiredate) from (–聚合函式count()不會計算為空的列，所以不必把null轉換,在下面一層就可以求出來
select sta,tno,hiredate,decode(tno,null,0,1) cnt from (
select level,add_months(trunc(min_date,'y'),level-1) sta from (
select min(hiredate) min_date,max(hiredate) max_date from tmp_a
)
connect by level <= months_between(add_months(trunc(max_date,'y'),12),trunc(min_date,'y')) ) t
left join tmp_a on sta = trunc(hiredate,'mm')
)group by sta
order by sta
20、按照給定的時間單位進行查詢
–問題:查詢與給定月份、星期幾或其他時間單位相匹配的日期。
–例如：找到2月份和12月份聘用的所有員工或者查詢星期二聘用的所有員工
with tmp_a as(
select 'aa' tno,to_date('19800506','yyyymmdd') hiredate from dual union all
select 'bb' tno,to_date('19830701','yyyymmdd') hiredate from dual union all
select 'cc' tno,to_date('19860702','yyyymmdd') hiredate from dual union all
select 'dd' tno,to_date('19810510','yyyymmdd') hiredate from dual union all
select 'ee' tno,to_date('19820503','yyyymmdd') hiredate from dual union all
select 'gg' tno,to_date('19820504','yyyymmdd') hiredate from dual union all
select 'ff' tno,to_date('19821015','yyyymmdd') hiredate from dual
)
select tno,hiredate,to_char(hiredate,'day'),to_char(hiredate,'mm') from tmp_a
where to_char(hiredate,'day') = '星期二' or to_char(hiredate,'mm') in('07','05')
21、使用日期的特殊部分比較記錄
–問題：查詢聘用日期月份和周內日期都相同的員工。例如，如果在1988年3月10日星期一聘用了某了員工，
–而在2001年3月2日星期一聘用了另一個員工，那麼，由於二者的聘用日期都在星期一，而且月份名一致，則可以認為他們相匹配
with tmp_a as(
select tno,hiredate,to_char(hiredate,'day') xingqi,to_char(hiredate,'mm') mon from (
select 'aa' tno,to_date('19800506','yyyymmdd') hiredate from dual union all
select 'bb' tno,to_date('19830701','yyyymmdd') hiredate from dual union all
select 'cc' tno,to_date('19860702','yyyymmdd') hiredate from dual union all
select 'dd' tno,to_date('19810510','yyyymmdd') hiredate from dual union all
select 'ee' tno,to_date('19820503','yyyymmdd') hiredate from dual union all
select 'tt' tno,to_date('20090512','yyyymmdd') hiredate from dual union all
select 'gg' tno,to_date('19820504','yyyymmdd') hiredate from dual union all
select 'ff' tno,to_date('19821015','yyyymmdd') hiredate from dual)
)
select * from tmp_a x,tmp_a y
where x.xingqi = y.xingqi and x.mon = y.mon and x.tno > y.tno
22、識別重疊的日期範圍
–問題：查詢員工在老工程結束之前就開始新工作的所有例項 當結束那天正好接了另一個專案也算
–一個開始時間在 另一行記錄的開始時間和結束時間之間
–方法一
with tmp_a as(
select 7782 empno,'clark' ename,1 proj_id,to_date('20050616','yyyymmdd') proj_start,to_date('20050618','yyyymmdd') proj_end from dual union all
select 7782 empno,'clark' ename,4 proj_id,to_date('20050619','yyyymmdd') proj_start,to_date('20050624','yyyymmdd') proj_end from dual union all
select 7782 empno,'clark' ename,7 proj_id,to_date('20050622','yyyymmdd') proj_start,to_date('20050625','yyyymmdd') proj_end from dual union all
select 7782 empno,'clark' ename,10 proj_id,to_date('20050625','yyyymmdd') proj_start,to_date('20050628','yyyymmdd') proj_end from dual union all
select 7782 empno,'clark' ename,13 proj_id,to_date('20050628','yyyymmdd') proj_start,to_date('20050702','yyyymmdd') proj_end from dual union all
select 7839 empno,'king' ename,2 proj_id,to_date('20050617','yyyymmdd') proj_start,to_date('20050621','yyyymmdd') proj_end from dual union all
select 7839 empno,'king' ename,8 proj_id,to_date('20050623','yyyymmdd') proj_start,to_date('20050625','yyyymmdd') proj_end from dual union all
select 7839 empno,'king' ename,14 proj_id,to_date('20050629','yyyymmdd') proj_start,to_date('20050630','yyyymmdd') proj_end from dual union all
select 7839 empno,'king' ename,11 proj_id,to_date('20050626','yyyymmdd') proj_start,to_date('20050627','yyyymmdd') proj_end from dual union all
select 7839 empno,'king' ename,5 proj_id,to_date('20050620','yyyymmdd') proj_start,to_date('20050624','yyyymmdd') proj_end from dual union all
select 7934 empno,'miller' ename,3 proj_id,to_date('20050618','yyyymmdd') proj_start,to_date('20050622','yyyymmdd') proj_end from dual union all
select 7934 empno,'miller' ename,12 proj_id,to_date('20050627','yyyymmdd') proj_start,to_date('20050628','yyyymmdd') proj_end from dual union all
select 7934 empno,'miller' ename,15 proj_id,to_date('20050630','yyyymmdd') proj_start,to_date('20050703','yyyymmdd') proj_end from dual union all
select 7934 empno,'miller' ename,9 proj_id,to_date('20050624','yyyymmdd') proj_start,to_date('20050627','yyyymmdd') proj_end from dual union all
select 7934 empno,'miller' ename,6 proj_id,to_date('20050621','yyyymmdd') proj_start,to_date('20050623','yyyymmdd') proj_end from dual
)select * from tmp_a x,tmp_a y
where x.ename = y.ename and x.proj_start <= y.proj_end and x.proj_id <> y.proj_id
and x.proj_start >= y.proj_start
–方法二
–從1開始找出不連續的值 用聯接 先產生一批連續的值,與所求表去聯接則可
with tmp_a as(
select level base from dual
connect by level <= 100000
)select * from tmp_a x,(
select 2 num from dual union all
select 5 num from dual union all
select 6 num from dual union all
select 9 num from dual union all
select 12 num from dual
)y where x.base = y.num(+)
and y.num is null
order by x.base