一年 = 365 天，一個月 = 31 天
Select 1 as year, 5 as month, 10 as day from dual
union all
Select 1 as year, 10 as month, 25 as day from dual
需要resault為：
years month day
3 4 4
uj5u.com熱心網友回復：
如果 1 年 = 365 天，1 個月 = 31 天，則：
SELECT TRUNC(SUM(year*365 month*31 day)/365) AS years,
TRUNC(MOD(SUM(month*31 day), 365)/31) AS month,
MOD(MOD(SUM(month*31 day), 365), 31) AS day
FROM
(
Select 1 as year, 5 as month, 10 as day from dual
union all
Select 1 as year, 13 as month, 25 as day from dual
) t1;
哪個輸出：
年 月 天 3 7 11
如果 1 年 = 12 個月和 1 個月 = 31 天，那么你想要：
SELECT TRUNC(SUM(year month/12 day/31/12)) AS years,
TRUNC(MOD(SUM(month day/31), 12)) month,
MOD(SUM(day) , 31) day
FROM
(
Select 1 as year, 5 as month, 10 as day from dual
union all
Select 1 as year, 13 as month, 25 as day from dual
) t1;
哪個輸出：
年 月 天 3 7 4
db<>在這里擺弄
uj5u.com熱心網友回復：
我們可以根據條件使用一些演算法來計算天數
one year = 365 daysone month = 31 days
- 年：（年 * 365 月 * 31 日）除以 365 得到數字的總年份。
- Month : (month * 31 day) 除以 31 得到總月份數，但我們需要從中獲取 mod，因為如果總月份數高于 12，則將攜帶總月份數。
作為這個查詢。
select CAST(SUM((year * 365 month * 31 day) / 365) AS INT) years ,
CAST(MOD(SUM(month * 31 day)/31, 12) AS INT) month,
MOD(SUM(day) , 31) day
from
(
Select 1 as year, 5 as month, 10 as day from dual
union all
Select 1 as year, 10 as month, 25 as day from dual
) t1;
sqlfiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/483092.html
上一篇：得到錯誤cx_Oracle.DatabaseError:ORA-01008:notallvariablesboundwhiletryingtobindthevalueofalistinamergest