求sql脚本月份选择代码

Question

我准备做一个自动化运维脚本，由sql脚本和shell脚本组成，让shell去调用sql脚本。然后将数据库查询出的结果保存到Excel文件中。需求描述为每个月的5号3:00自动执行该脚本，调用sql脚本去数据库查上个月每天的系统交易量，难点就是如何通过根据数据库系统时间判定上个月是大小月并执行语句。
（1）shell脚本比较简单，我已经写好了，内容如下：
#!/bin/bash
export ORACLE_HOME=/oracle/product/10.2.0/db_1
#####################
/oracle/product/10.2.0/db_1/bin/sqlplus appuser/unicom@DB06ST1_159 @/app/yunwei/test/test.sql
chmod 777 -R /app/yunwei

（2）请大神帮我改写一下sql，运用变量赋值的方法,也就是把日期改掉，如果要加上变量什么的，请加上。
select cutoffday,count(*) from tlog_all t where t.cutoffday = 20131201 group by cutoffday union
select cutoffday,count(*) from tlog_all t where t.cutoffday = 20131202 group by cutoffday union
select cutoffday,count(*) from tlog_all t where t.cutoffday = 20131203 group by cutoffday union
........
select cutoffday,count(*) from tlog_all t where t.cutoffday = 20131231 group by cutoffday

（3）我能想出来的方法是：select cutoffday,count(*) from tlog_all t where t.cutoffday =(select to_char(sysdate-30,'yyyymmdd') from dual)，但是判断大小月自己不太会写。

（4）悬赏100分，如果能在您的帮助下我的脚本能够执行成功， 我会再额外给您100分。

Answer 1

select cutoffday,count(*) 
from tlog_all  t  where t.cutoffday =to_char(trunc(sysdate-30),'mm'),'yyyymmdd') 
union all 
select cutoffday,count(*) 
from tlog_all  t  where t.cutoffday =to_char(trunc(sysdate-30),'mm')+1,'yyyymmdd') 
union all 
select cutoffday,count(*) 
from tlog_all  t  where t.cutoffday =to_char(trunc(sysdate-30),'mm')+2,'yyyymmdd')
...
union all
select cutoffday,count(*) 
from tlog_all  t  where t.cutoffday =to_char(trunc(sysdate-30),'mm')+28,'yyyymmdd')
union all
select cutoffday,count(*) 
from tlog_all  t  where t.cutoffday =(case when trunc(sysdate-30),'mm')+29=trunc(sysdate,'mm') then null else 
to_char(trunc(sysdate-30),'mm')+29,'yyyymmdd')
)
union all
select cutoffday,count(*) 
from tlog_all  t  where t.cutoffday =(case when trunc(sysdate-30),'mm')+30=trunc(sysdate,'mm') then null else 
to_char(trunc(sysdate-30),'mm')+30,'yyyymmdd')
)
union all
select cutoffday,count(*) 
from tlog_all  t  where t.cutoffday =(case when trunc(sysdate-30),'mm')+31=trunc(sysdate,'mm') then null else 
to_char(trunc(sysdate-30),'mm')+31,'yyyymmdd')
)

你看看是不是这个意思？

追问

就是大月31天，小月30天，还有一个2月28天，能否写一个判断月份是大小月以及2月后在进行赋值计算的sql脚本？

追答

写的有些问题，改一下

select cutoffday,count(*) 
from tlog_all  t  where t.cutoffday =to_char(trunc(sysdate-30),'mm'),'yyyymmdd')
union all 
select cutoffday,count(*) 
from tlog_all  t  where t.cutoffday =to_char(trunc(sysdate-30),'mm')+1,'yyyymmdd')
...
union all
select cutoffday,count(*) 
from tlog_all  t  where t.cutoffday =to_char(trunc(sysdate-30),'mm')+27,'yyyymmdd')
union all
select cutoffday,count(*) 
from tlog_all  t  where t.cutoffday =(case when trunc(sysdate-30),'mm')+28=trunc(sysdate,'mm') then null else 
to_char(trunc(sysdate-30),'mm')+28,'yyyymmdd')
)
union all
select cutoffday,count(*) 
from tlog_all  t  where t.cutoffday =(case when trunc(sysdate-30),'mm')+29=trunc(sysdate,'mm') then null else 
to_char(trunc(sysdate-30),'mm')+29,'yyyymmdd')
)
union all
select cutoffday,count(*) 
from tlog_all  t  where t.cutoffday =(case when trunc(sysdate-30),'mm')+30=trunc(sysdate,'mm') then null else 
to_char(trunc(sysdate-30),'mm')+3,'yyyymmdd')
)

追问

我先单条执行验证，报错，”SQL命令未正确结束“，是不是标点原因？麻烦检查一下？

追答

我想了一下，你直接用下面的sql语句就行了。

SELECT cutoffday,count(*) FROM (
SELECT trunc(add_months(SYSDATE,-1),'mm')+LEVEL-1 dat FROM dual connect BY LEVEL <=31 
)t LEFT JOIN tlog_all ON to_char(t.dat,'yyyymmdd')=cutoffday
WHERE t.dat <> TRUNC(SYSDATE,'mm')
GROUP BY cutoffday
order BY 1

追问

前28天的没问题，从第29天开始，我单条验证，还是报错”缺失关键字“，您再看看？

追答

你试下我上面的sql语句。

追问

扔进plsql执行了，由于是联通后台数据库，月统计量非常庞大，就怕sql不够优化。先谢谢您了，我拿着您这个代码再去问问我们的DBA，谢谢了

追答

说一下：

SELECT cutoffday,count(*) FROM (
SELECT trunc(add_months(SYSDATE,-1),'mm')+LEVEL-1 dat FROM dual connect BY LEVEL <=31 
)t LEFT JOIN tlog_all ON to_char(t.dat,'yyyymmdd')=cutoffday
WHERE t.dat <> TRUNC(SYSDATE,'mm')
GROUP BY cutoffday
order BY 1

上面的语句，打个比方如果12月1号没有数据，那么12月1号就不会出现在结果集中。

SELECT t.dat,count(*) FROM (
SELECT trunc(add_months(SYSDATE,-1),'mm')+LEVEL-1 dat FROM dual connect BY LEVEL <=31 
)t LEFT JOIN tlog_all ON to_char(t.dat,'yyyymmdd')=cutoffday
WHERE t.dat <> TRUNC(SYSDATE,'mm')
GROUP BY t.dat
order BY 1
;

这个语句好处是比如12月1号个数为0，12月1号它也会出现在结果集中，count(*) 为0。

看你的业务，选择哪个比较合适。

本回答被提问者采纳

Answer 2

select cutoffday,count(*) from tlog_all  t  where substr(t.cutoffday,1,6)=to_char(trunc(add_months(sysdate,-1)),'yyyymm') group by cutoffday

 这样吧

主要就是add_months函数，你可以查一下这个函数的用法

Answer 3

你确定一下cutoffday 是date型还是 char/varchar型，如果是date型，一个语句搞定，语句如下
select cutoffday,count(*) from tlog_all t where t.cutoffday < trunc(sysdate ,'mm') and t.cutoffday >= add_months( trunc(sysdate ,'mm'),-1)
group by cutoffday