第七章 日志运算
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.确定一年中周内各日期的次数
文章来源地址https://www.toymoban.com/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 天数
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);
文章来源:https://www.toymoban.com/news/detail-503099.html
到了这里,关于Oracle 查询优化改写(第七章)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!