我有一个查询，在开始日期和结束日期之间给出年和月的输出

Question

我有一个查询，在开始日期和结束日期之间给出年和月的输出。

查询:

SELECT lease.lease_start_date,
lease.lease_end_date,
(lease.lease_end_date - lease.lease_start_date) YEAR TO MONTH AS year_months 
FROM lease_header lease

输出:

start_date  End_date    Year-Month
03-OCT-18   16-NOV-18   +00-01
28-SEP-18   28-SEP-19   +01-00
03-OCT-18   31-DEC-19   +01-03

从这个输出，我将只需要年-月显示在我的屏幕上，现在它将显示如下0年1个月，但如果年份为零，我只需要显示月份，否则同时显示年和月。

我需要如下输出:

if the year is 0 then show as 1 month
if the year is not 0 then show as 1 year 0 month

Answer 1

基本查询提供了所需的结果。然后你可以玩跳圈来获得显示游戏。首先将年份-月份分隔为单独的列，然后应用case获取年份(s)文本段并连接月份段。

with lease_header (start_date, end_date) as 
( select to_date('03-OCT-18', 'dd-mon-rr'), to_date('16-NOV-18', 'dd-mon-rr') from dual union all  
select to_date('28-SEP-18', 'dd-mon-rr'), to_date('28-SEP-19', 'dd-mon-rr') from dual union all 
select to_date('03-OCT-18', 'dd-mon-rr'), to_date('31-DEC-19', 'dd-mon-rr') from dual 
) 
-- your query starts here 
select start_date
, end_date
, case when yrs = 0 
then null
else to_char(yrs) || ' year ' 
end 
|| to_char(mth) || ' month' year_months           
from (
select start_date
, end_date
, extract (year from yr_mth) yrs
, extract (month from yr_mth) mth
from ( 
select start_date
, end_date  
, ( end_date -  start_date) year to month  as yr_mth
from lease_header lease
)
);