从表中取数据如果当前时间是月末取表中这月末和上月末数据,如果不是月末就取昨天数据和上个月昨天数据。
个人想法:
如果月末:select * from dm.DMPBCS_PRODUCT_L_M_FCT
where period_id=
to_number(TO_CHAR(LAST_DAY(ADD_MONTHS(LAST_DAY(SYSDATE) + 1, -1)),'yyyyMMdd'))
and period_id=
TO_number(TO_CHAR(LAST_DAY(ADD_MONTHS(LAST_DAY(SYSDATE) + 1, -2)),'yyyyMMdd'))
如果非月末:select * from dm.DMPBCS_PRODUCT_L_M_FCT
where period_id=
to_number(TO_CHAR(ADD_MONTHS(SYSDATE, 0), 'yyyyMMdd'))-1
and period_id=
to_number(TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyyMMdd'))-1;也不知道自己思路对不,求各位帮忙,谢谢。
个人想法:
如果月末:select * from dm.DMPBCS_PRODUCT_L_M_FCT
where period_id=
to_number(TO_CHAR(LAST_DAY(ADD_MONTHS(LAST_DAY(SYSDATE) + 1, -1)),'yyyyMMdd'))
and period_id=
TO_number(TO_CHAR(LAST_DAY(ADD_MONTHS(LAST_DAY(SYSDATE) + 1, -2)),'yyyyMMdd'))
如果非月末:select * from dm.DMPBCS_PRODUCT_L_M_FCT
where period_id=
to_number(TO_CHAR(ADD_MONTHS(SYSDATE, 0), 'yyyyMMdd'))-1
and period_id=
to_number(TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyyMMdd'))-1;也不知道自己思路对不,求各位帮忙,谢谢。
解决方案 »
- for 循环
- 某公司的一个面试题求解(不是很难)
- 关于动态游标和静态游标的比较疑问,大家都进来看看啦~~~~~~
- 在oracle9i的OEM下创建作业出错,错误提示ora-00972:identifier is too long,请各位大虾解释解释是什么原因
- 如用SQL语句导出oracle某一用户的数据,我想通过SQL用PB程序来控制,不是在DOS下
- 怎样在ORACLE里设置一个递增的主键值?
- 在XP上装ORACLE8.1.7的客户端怎么是英文界面?
- 版本与语言的转换问题
- 谁能解决这个问题(在winXP Pressional下安装Oracle9i遇到的)?
- 新手提问:连接不上oracle sql developer
- 如何按日统计!!
- 写一个shell脚本执行sql语句
if 是月末天 then
sql=""
else
sql=""
end ifexecute sql
select *
from dm.DMPBCS_PRODUCT_L_M_FCT
where (
period_id = case when trunc(sysdate)=trunc(last_day(sysdate)) --如果是月末
then to_number(TO_CHAR(ADD_MONTHS(SYSDATE, -1),'yyyyMMdd')) --上月最后一天
else --不是月末
to_number(TO_CHAR(ADD_MONTHS(SYSDATE-1, -1),'yyyyMMdd')) --上月昨天 end) or(
period_id = case when trunc(sysdate)=trunc(last_day(sysdate)) --如果是月末
then to_number(TO_CHAR(SYSDATE,'yyyyMMdd')) --本月月末
else --不是月末
to_number(TO_CHAR(SYSDATE-1,'yyyyMMdd')) --昨天
end);
select * from dm.DMPBCS_PRODUCT_L_M_FCT
where period_id in(to_number(TO_CHAR(LAST_DAY(ADD_MONTHS(LAST_DAY(SYSDATE) + 1, -1)),'yyyyMMdd')),TO_number(TO_CHAR(LAST_DAY(ADD_MONTHS(LAST_DAY(SYSDATE) + 1, -2)),'yyyyMMdd')))
and trunc(sysdate)<>trunc(last_day(sysdate))
union all
select * from dm.DMPBCS_PRODUCT_L_M_FCT
where period_id in(to_number(TO_CHAR(ADD_MONTHS(SYSDATE, 0), 'yyyyMMdd'))-1,to_number(TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyyMMdd'))-1)
and trunc(sysdate)<>trunc(last_day(sysdate))
trunc(last_day(date_field) = trunc(sysdate)
and period_id in ( trunc(sysdate), trunc(last_day(add_month(sysdate,-1)))
)
or
(
trunc(last_day(date_field) <> trunc(sysdate)
and
period_id in( trunc(sysdate-1), trunc(add_month(sysdate-1, -1)))
)
select * from dm.DMPBCS_PRODUCT_L_M_FCT
where period_id in(to_number(TO_CHAR(LAST_DAY(ADD_MONTHS(LAST_DAY(SYSDATE) + 1, -1)),'yyyyMMdd')),TO_number(TO_CHAR(LAST_DAY(ADD_MONTHS(LAST_DAY(SYSDATE) + 1, -2)),'yyyyMMdd')))
and trunc(sysdate)=trunc(last_day(sysdate))
union all
select * from dm.DMPBCS_PRODUCT_L_M_FCT
where period_id in(to_number(TO_CHAR(ADD_MONTHS(SYSDATE, 0), 'yyyyMMdd'))-1,to_number(TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyyMMdd'))-1)
and trunc(sysdate)<>trunc(last_day(sysdate))
from tbl t1,
(select case when trunc(sysdate) = trunc(last_day(sysdate)) then trunc(sysdate)
else trunc(sysdate -1)
end sdate
from dual) t2
where to_date(t1.sdate, 'yyyy-mm-dd') = t2.sdate
or to_date(t1.sdate, 'yyyy-mm-dd') = add_months(t2.sdate, -1);
没有小S 是失误