判断当前日期来决定是否执行sql 求助求助！！
做报表统计 查询条件是年份 如果是往年的记录就显示12个月份的记录 如果是今年的就显示当前月份之前的记录 我真的是没思路了 润乾报表 函数或是sql能解决问题的办法都可以
select distinct(quota.id),01 as month,quota.use_car_plan as use_car_plan,interior.sum_interior as sum_interior,exterior.sum_exterior
,jiedai.sum_jiedai,huiyi.sum_huiyi,peixun.sum_peixun,gongyong.sum_gongyong,total.total_sum from(
(select quota.dept_id as id ,quota.dept_name,quota.use_car_plan as use_car_plan from dept_quota quota
where quota.year=? and quota.dept_id=?)quota
left join
(select work.leader_dept_id as id, sum(work.total_fee) as sum_interior from car_apply_work work where work.leader_dept_id=? and substr
(work.ride_time,1,4)=?
and substr(work.ride_time,6,2)='01' and
work.dispatch_way='junei' and work.purpose='chailv'
and work.state='close'group by work.leader_dept_id) interior on
quota.id=interior.id
left join
(select work.leader_dept_id as id,sum(work.total_fee) as sum_exterior from car_apply_work work where work.leader_dept_id=? and
work.dispatch_way<> 'junei' and work.purpose='chailv'
and work.state='close' and substr(work.ride_time,6,2)='01'
and substr(work.ride_time,1,4)=?
group by work.leader_dept_id) exterior on quota.id=exterior.id
left join
(select work.leader_dept_id as id,sum(work.total_fee) as sum_jiedai from car_apply_work work where work.leader_dept_id=? and
work.purpose='jiedai'
and work.state='close' and substr(work.ride_time,6,2)='01'
and substr(work.ride_time,1,4)=? group by work.leader_dept_id) jiedai on quota.id=jiedai.id
left join
(select work.leader_dept_id as id,sum(work.total_fee) as sum_huiyi from car_apply_work work where work.leader_dept_id=? and
work.purpose='huiyi'
and work.state='close' and substr(work.ride_time,6,2)='01'
and substr(work.ride_time,1,4)=? group by work.leader_dept_id) huiyi on quota.id=huiyi.id
left join
(select work.leader_dept_id as id,sum(work.total_fee) as sum_peixun from car_apply_work work where work.leader_dept_id=? and
work.purpose='peixun'
and work.state='close' and substr(work.ride_time,6,2)='01'
and substr(work.ride_time,1,4)=? group by work.leader_dept_id) peixun on
quota.id=peixun.id
left join
(select work.leader_dept_id as id,sum(work.total_fee) as total_sum from car_apply_work work where work.leader_dept_id=?
and work.purpose='chailv'
and work.state='close' and substr(work.ride_time,6,2)<='01'
and substr(work.ride_time,1,4)=?
group by work.leader_dept_id) total on quota.id=total.id
left join
(select work.leader_dept_id as id,sum(work.total_fee) as sum_gongyong from car_apply_work work where work.leader_dept_id=? and
work.purpose='gongyong'
and work.state='close' and substr(work.ride_time,6,2)='01'
and substr(work.ride_time,1,4)=? group by work.leader_dept_id) gongyong on
quota.id=gongyong.id)
union all
select distinct(quota.id),02 as month,quota.use_car_plan as use_car_plan,interior.sum_interior as sum_interior,exterior.sum_exterior
,jiedai.sum_jiedai,huiyi.sum_huiyi,peixun.sum_peixun,gongyong.sum_gongyong ,total.total_sum from(
(select quota.dept_id as id ,quota.dept_name,quota.use_car_plan as use_car_plan from dept_quota quota
where quota.year=? and quota.dept_id=?)quota
left join
(select work.leader_dept_id as id, sum(work.total_fee) as sum_interior from car_apply_work work where work.leader_dept_id=? and substr
(work.ride_time,1,4)=?
and substr(work.ride_time,6,2)='02' and
work.dispatch_way='junei' and work.purpose='chailv'
and work.state='close'group by work.leader_dept_id) interior on
quota.id=interior.id
left join
(select work.leader_dept_id as id,sum(work.total_fee) as sum_exterior from car_apply_work work where work.leader_dept_id=? and
work.dispatch_way<> 'junei' and work.purpose='chailv'
and work.state='close' and substr(work.ride_time,6,2)='02'
and substr(work.ride_time,1,4)=?
group by work.leader_dept_id) exterior on quota.id=exterior.id
left join
(select work.leader_dept_id as id,sum(work.total_fee) as sum_jiedai from car_apply_work work where work.leader_dept_id=? and
work.purpose='jiedai'
and work.state='close' and substr(work.ride_time,6,2)='02'
and substr(work.ride_time,1,4)=? group by work.leader_dept_id) jiedai on quota.id=jiedai.id
left join
(select work.leader_dept_id as id,sum(work.total_fee) as sum_huiyi from car_apply_work work where work.leader_dept_id=? and
work.purpose='huiyi'
and work.state='close' and substr(work.ride_time,6,2)='02'
and substr(work.ride_time,1,4)=? group by work.leader_dept_id) huiyi on quota.id=huiyi.id
left join
(select work.leader_dept_id as id,sum(work.total_fee) as sum_peixun from car_apply_work work where work.leader_dept_id=? and
work.purpose='peixun'
and work.state='close' and substr(work.ride_time,6,2)='02'
and substr(work.ride_time,1,4)=? group by work.leader_dept_id) peixun on
quota.id=peixun.id
left join
(select work.leader_dept_id as id,sum(work.total_fee) as total_sum from car_apply_work work where work.leader_dept_id=?
and work.purpose='chailv'
and work.state='close' and substr(work.ride_time,6,2)<='02'
and substr(work.ride_time,1,4)=?
group by work.leader_dept_id) total on quota.id=total.id
left join
(select work.leader_dept_id as id,sum(work.total_fee) as sum_gongyong from car_apply_work work where work.leader_dept_id=? and
work.purpose='gongyong'
and work.state='close' and substr(work.ride_time,6,2)='02'
and substr(work.ride_time,1,4)=? group by work.leader_dept_id) gongyong on
quota.id=gongyong.id)
这是两个月的查询结果记录
[解决办法]
传入一个参数 年份 构造一个月份表来左联你的表 查询每月数据
select to_char(add_months(to_date('2011'||'01-01','yyyy-mm-dd'),level-1),'yyyy-mm') monfrom dualconnect by level <= (select case when 2011=to_char(sysdate,'yyyy') then to_number(to_char(sysdate,'mm')) else 12 end a from dual)--参数2011 mon-----------------1 2011-012 2011-023 2011-034 2011-045 2011-056 2011-067 2011-078 2011-089 2011-0910 2011-1011 2011-1112 2011-12--参数2012 mon-----------------1 2012-012 2012-023 2012-034 2012-04
[解决办法]
这一大堆SQL看的人眼晕……下面是按自己理解写的
-- 如果是往年的记录就显示12个月份的记录 如果是今年的就显示当前月份之前的记录,根据年份判断可以得出时间范围，一个开始时间，一个结束时间，以这两个时间作为条件查询
--例如传入年份year(格式yyyy)
--得到开始时间（闭区间）:不管传入的是哪一年，肯定都是从传入年份的一月一日开始查
select to_date(year||'0101','yyyymmdd') from dual;
--得到结束时间（开区间）：如果是往年，传入年份第二年的1月1日；如果是今年，本月1日
select decode(to_number(to_char(sysdate, 'yyyy')) - to_number(year),
0,
to_date(to_char(sysdate, 'yyyymm') || '01', 'yyyymmdd'),
to_date((to_number(year) + 1) || '0101', 'yyyymmdd'))
from dual;
--比如时间字段是time,where条件就是：
where time<decode(to_number(to_char(sysdate, 'yyyy')) - to_number(year),
0,
to_date(to_char(sysdate, 'yyyymm') || '01', 'yyyymmdd'),
to_date((to_number(year) + 1) || '0101', 'yyyymmdd'))
and time>=to_date(year||'0101','yyyymmdd')