1.通过自动取当前上个月一号到上个月底的方案实现
-- 取上个月第一天
SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -2)) + 1,'YYYYMMDD') FROM DUAL;
-- 查询结果:20160801
-- 取上个月最后一天
SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)),'YYYYMMDD') FROM DUAL;
-- 查询结果:20160831
实现SQL:
select sum(t.totalquantity) 交易值,
(case
when t.buysubject = 'ZJ' then
'浙江'
when t.buysubject = 'FJ' then
'福建'
when t.buysubject = 'JS' then
'江苏'
when t.buysubject = 'HD' then
'华东'
when t.buysubject = 'AH' then
'安徽'
when t.buysubject = 'SH' then
'上海'
else
t.buysub
END) name,
'双边' 交易范围,
${数据时间} 时间
from temp t
where tome between
to_date(TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -2)) + 1, 'YYYYMMDD'),
'yyyymmdd') and
to_date(TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)), 'YYYYMMDD'),
'yyyymmdd')
group by t.buysub
2.摒弃between and 去取值,把日期类型转成YYYYMM格式字符串,如:201608 直接用where去判断修改后的SQL如下:
where to_char(conbgdate, 'YYYYMM') =
to_char(LAST_DAY(ADD_MONTHS(SYSDATE, -1)), 'YYYYMM')
group by t.buysubject