最近项目中的查询模块中,查询条件用到了年月日下拉框,默认下拉框中都要填充数据,分别是年、月、日,并且还要实现级联要求。这就需要根据不同的年填充月份数据,不同的月填充不同的日,填充的数据需要查询语句完成。通过努力终于实现了想要的结果,如下:
1.年份:
select (1 - level +to_char(sysdate, 'yyyy')) years
from dual
connect by level <=10
2.月份:
SELECT CASE WHEN mons<10 THEN '0'||mons ELSE to_char(mons) END monsNew FROM
(
select(1 - level + decode(to_char(sysdate, 'yyyy'),
'?年份', --参数,本年只填充当前月份以前的时间
to_char(sysdate, 'MM'),12)) mons
from dual
connect by level <= decode(to_char(sysdate, 'yyyy'),
'?年份',
to_char(sysdate, 'MM'),
12))
3.日期:
SELECT CASE WHEN days<10 THEN '0'||days ELSE to_char(days) END daysNew FROM
(
select (1 - level + decode(to_char(sysdate, 'yyyy'),
'?年份',
decode(to_char(sysdate, 'MM'),
'?月份',
to_number(to_char(sysdate, 'dd')),
to_number(to_char(last_day(to_date('?年份?月份'','yyyyMM')),'dd'))),
to_number(to_char(last_day(to_date('?年份?月份'','yyyyMM')),'dd')))) days
from dual
connect by level <= decode(to_char(sysdate, 'yyyy'),
'?年份',
decode(to_char(sysdate, 'MM'),'08', to_number(to_char(sysdate, 'dd')),to_number(to_char(last_day(to_date
('?年份?月份','yyyyMM')),'dd'))),
to_number(to_char(last_day(to_date('?年份?月份'','yyyyMM')),'dd')))
)
感觉这语句还是非常用得着的,如果大家还有其它好的语句,希望能拿出来分享~~~