判断当前日期来决定是否执行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)
这是两个月的查询结果记录

[forgetsam]

[Quote=引用 5 楼 的回复:]

我也这么想过 但是假如一月份的数据没有值查询结果为空 我得做处理 为空的设为0 所以就会导致本年大于当前月份的值也为0了
[/Quote]

统计当然是连年带月一起统计。

你没表没数据，给个SQL有什么用，打几个田字格都能说明白的问题说不明白。

[zhaodan2000]

[Quote=引用 5 楼 的回复:]
引用 3 楼 的回复:
应该也不用这么判断吧 如果数据库里面有时间字段 那直接根据月份统计下来就可以了 如果是2011年 每个月都有数据 那统计下来就是12个月 如果是今年 那肯定只有4个月的数据 只能统计到4个月咯

我也这么想过 但是假如一月份的数据没有值查询结果为空 我得做处理 为空的设为0 所以就会导致本年大于当前月份的值也为0了
[/Quote]
条件设成to_char(date,'yyyy-MM')<to_char(sysdate,'yyyy-MM')试试

[大明湖畔_帅锅]

那试着用构造月份来关联表咯

[大明湖畔_帅锅]

传入一个参数 年份 构造一个月份表来左联你的表 查询每月数据

select to_char(add_months(to_date('2011'||'01-01','yyyy-mm-dd'),level-1),'yyyy-mm') mon
from dual
connect 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-01
2	2011-02
3	2011-03
4	2011-04
5	2011-05
6	2011-06
7	2011-07
8	2011-08
9	2011-09
10	2011-10
11	2011-11
12	2011-12


--参数2012
      mon
-----------------
1	2012-01
2	2012-02
3	2012-03
4	2012-04

[nxvan]

如果表里面是时间字段
select * from tab where to_char(date,'yyyy') = 输入的年份;
这样是否可以
另外，头像哪儿来的呀，好萌的妹子

[nxvan]

好吧，，不知道是网页问题还是什么，我没看到LZ下面的一大串东西，，囧了
[Quote=引用 1 楼 的回复:]
如果表里面是时间字段
select * from tab where to_char(date,'yyyy') = 输入的年份;
这样是否可以
另外，头像哪儿来的呀，好萌的妹子
[/Quote]

[大明湖畔_帅锅]

应该也不用这么判断吧 如果数据库里面有时间字段 那直接根据月份统计下来就可以了 如果是2011年 每个月都有数据 那统计下来就是12个月 如果是今年 那肯定只有4个月的数据 只能统计到4个月咯

[大明湖畔_帅锅]

不知道这表是怎么设计的 怎么看着昏昏的哇 查询一个月要连接这么多次么

[瞬间]

[Quote=引用 3 楼 的回复:]
应该也不用这么判断吧 如果数据库里面有时间字段 那直接根据月份统计下来就可以了 如果是2011年 每个月都有数据 那统计下来就是12个月 如果是今年 那肯定只有4个月的数据 只能统计到4个月咯
[/Quote]
我也这么想过 但是假如一月份的数据没有值查询结果为空 我得做处理 为空的设为0 所以就会导致本年大于当前月份的值也为0了

[buryMyLove]

这一大堆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')

[瞬间]

[Quote=引用 9 楼 的回复:]
传入一个参数 年份 构造一个月份表来左联你的表 查询每月数据

SQL code


select to_char(add_months(to_date('2011'||'01-01','yyyy-mm-dd'),level-1),'yyyy-mm') mon
from dual
connect by level <= (select case when 2011=to_char(s……
[/Quote]
很感谢 刚看到 我是用报表函数实现此功能的