Livebos视图

create or replace view t_sys_period
(id, fno, fname, ftype, fstartdate, fenddate)
as
select 1||YEAR || case when (MONTH<10) then '0' else '' end ||MONTH|| case when (DAY<10) then '0' else '' end ||DAY,'Y'||YEAR ||'M'||case when (MONTH<10) then '0' else '' end ||MONTH||'D'||case when (DAY<10) then '0' else '' end ||DAY, YEAR ||'年'||case when (MONTH<10) then '0' else '' end ||MONTH||'月'||case when (DAY<10) then '0' else '' end ||DAY||'日',1,RQ,RQ from tcalendar
union
select 2||YEAR || case when (NOOFWEEK<10) then '0' else '' end ||NOOFWEEK,'Y'||YEAR ||'W' || case when (NOOFWEEK<10) then '0' else '' end ||NOOFWEEK, YEAR ||'年第' || case when (NOOFWEEK<10) then '0' else '' end ||NOOFWEEK ||'周',2,
case when NOOFWEEK=1 and count(week)<7 then (select max(rq) from tcalendar where week=1 and month=12 and year=t.year-1 ) else  min(RQ) end,max(RQ)
from tcalendar t
having (case when NOOFWEEK>50 and count(week)<>7 then 0 else 1 end)=1
 group by YEAR ,NOOFWEEK
union
select 3||YEAR || case when (MONTH<10) then '0' else '' end ||MONTH,'Y'||YEAR ||'M' || case when (MONTH<10) then '0' else '' end ||MONTH, YEAR ||'年' || case when (MONTH<10) then '0' else '' end ||MONTH ||'月',3,min(RQ),max(RQ) from tcalendar group by YEAR ,MONTH
union
select 4||YEAR ||Quarter, 'Y'||YEAR ||'Q'||Quarter,YEAR ||'年第' ||Quarter ||'季度',4,min(RQ),max(RQ) from tcalendar group by YEAR ,Quarter
union
select 5||YEAR, 'Y'||YEAR,YEAR  ||'年',5,min(RQ),max(RQ) from tcalendar group by YEAR;

转载于:https://www.cnblogs.com/luhanzhen/p/8624711.html