第七章 日志運算

1 加減年月日時分秒 +替換為-

--加減年月日時分秒 +替換為-
SELECT Add_Months(SYSDATE, 5 * 12) AS 加5年,
       Add_Months(SYSDATE, 5) AS 加5月，
       SYSDATE + 5 AS 加5天,
       SYSDATE + 5 / 24 AS 加5小時,
       SYSDATE + 5 / 24 / 60 AS 加5分鐘,
       SYSDATE + 5 / 24 / 60 / 60 AS 加5秒
  FROM Emp;
  

2.日期間隔年月日時分秒

--日期間隔年月日時分秒
SELECT Months_Between(Date1, Date2) / 12 as 年，
       Months_Between(Date1, Date2)  as 月，
       date3 as 日，
       date3*24 時,
       date3*24*60 分,
       date3*24*60*60 秒 
  FROM (SELECT MAX(Hiredate) Date1,
               MIN(Hiredate) Date2,
               MAX(Hiredate) - MIN(Hiredate) Date3
          FROM Emp);

--查詢所有員工入職以來的工作期限，用“XX年XX月XX日”的形式表示。
SELECT Ename,
       Trunc(Months_Between(SYSDATE, Hiredate) / 12) || '年' ||
       Trunc(MOD((Months_Between(SYSDATE, Hiredate)), 12)) || '月'||
       trunc(sysdate-add_months(hiredate,Months_Between(SYSDATE, Hiredate))) || '日'
  FROM Emp

3.確定兩個日期之間的工作天數(shù)

--篩選原始數(shù)據(jù)
--通過max()和min()轉(zhuǎn)為一行
--枚舉兩個日期之間的天數(shù)要加1，比如1到2是兩天，有兩條數(shù)據(jù)（2-1）+1
--通過與T5OO做笛卡爾積枚舉30天的所有日期。
--根據(jù)這些日期得到對應的工作日信息。
--進行過濾操作。
create table T500 As select level as ID from dual connect by level<=500

SELECT SUM(CASE
             WHEN To_Char(Min_Hd + T500.Id - 1,
                          'DY',
                          'NLS_DATE_LANGUAGE = American') IN ('SAT', 'SUN') THEN
              0
             ELSE
              1
           END) AS 工作天數(shù)
  FROM (SELECT MIN(Hiredate) AS Min_Hd, MAX(Hiredate) AS Max_Hd
          FROM Emp
         WHERE Ename IN ('BLAKE', 'JONES')) x,
       T500
 WHERE T500.Id <= Max_Hd - Min_Hd + 1;

SELECT COUNT(*)
  FROM (SELECT 日期,
               To_Char(日期， 'DY', 'NLS_DATE_LANGUAGE = American') AS Dy
          FROM (SELECT Min_Hd + (T500.Id - 1) AS 日期
                  FROM (SELECT MIN(Hiredate) AS Min_Hd,
                               MAX(Hiredate) AS Max_Hd
                          FROM Emp
                         WHERE Ename IN ('BLAKE', 'JONES')) x,
                       T500
                 WHERE T500.Id <= ((Max_Hd - Min_Hd) + 1)))
 WHERE Dy NOT IN ('SAT', 'SUN');

4.確定一年中周內(nèi)各日期的次數(shù)

文章來源地址http://www.zghlxwxcb.cn/news/detail-503099.html

WITH X0 AS
  (SELECT To_Date('2013-01-01', 'yyyy-mm-dd') AS 年初 FROM Dual),
 X1 AS
  (SELECT 年初, Add_Months(年初, 12) AS 年底 FROM X0),
 X2 AS
  (SELECT Next_Day(年初 - 1, LEVEL) AS D1, Next_Day(年底 - 8, LEVEL) AS D2
     FROM X1
   CONNECT BY LEVEL <= 7)
 SELECT To_Char(D1, 'dy') AS 星期, ((D2 - D1) / 7 + 1) AS 天數(shù)
   FROM X2;

5.確定當前記錄和下一條記錄之間相差的天數(shù)

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);

到了這里，關(guān)于Oracle 查詢優(yōu)化改寫（第七章）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！