Oracle 查询优化改写（第七章）

第七章 日志运算

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.确定两个日期之间的工作天数

--筛选原始数据
--通过max()和min()转为一行
--枚举两个日期之间的天数要加1，比如1到2是两天，有两条数据（2-1）+1
--通过与T5OO做笛卡尔积枚举30天的所有日期。
--根据这些日期得到对应的工作日信息。
--进行过滤操作。
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 工作天数
  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.确定一年中周内各日期的次数

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 天数
   FROM X2;

5.确定当前记录和下一条记录之间相差的天数

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