请问用一个sql查询去年本月同期

select to_char(CREDATE,'mm') CREDATE,to_char(CREDATE,'yyyy-mm') yearn,to_char(CREDATE,'yyyy') yearns,nvl(sum(inqty),0)inqty,nvl(sum(OUTQTY),0) OUTQTY

from BMS_ST_IO_UNION_V

group by to_char(CREDATE,'mm'),to_char(CREDATE,'yyyy'),to_char(CREDATE,'yyyy-mm') order by CREDATE,inqty,OUTQTY,yearn

ZAW 发布于 2021-6-4 09:47

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：6 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共2回答

最佳回答

liu.yangLv2初级互助
发布于2021-6-4 09:55（编辑于 2021-6-4 10:40）

select sum(case when to_char(CREDATE,'yyyy')=to_char(add_months(sysdate,-12),'yyyy') then inqty else 0 end) as "去年inqty", sum(case when to_char(CREDATE,'yyyy')=to_char(add_months(sysdate,-12),'yyyy') then OUTQTY else 0 end) as "去年OUTQTY", sum(case when to_char(CREDATE,'yyyymm')=to_char(sysdate,'yyyymm') then inqty else 0 end) as "本月inqty", sum(case when to_char(CREDATE,'yyyymm')=to_char(sysdate,'yyyymm') then OUTQTY else 0 end) as "本月OUTQTY", sum(case when to_char(CREDATE,'yyyymm')=to_char(add_months(sysdate,-12),'yyyymm') then inqty else 0 end) as "同期inqty", sum(case when to_char(CREDATE,'yyyymm')=to_char(add_months(sysdate,-12),'yyyymm') then OUTQTY else 0 end) as "同期OUTQTY"from BMS_ST_IO_UNION_V

举报收起评论(8)

ZAW(提问者) 这个${time}可以不传吗

2021-06-04 10:01

liu.yang 回复 ZAW(提问者) 可以啊这个就是参数你可以写固定日期都行

2021-06-04 10:02

ZAW(提问者) 回复 liu.yang 那我这个不写 ${time} 替换那个字段是CREDATE这个嘛

2021-06-04 10:04

liu.yang 回复 ZAW(提问者) CREDAT 是你表的日期字段把， ${time} 是你想查询那一天的数据可以是那个月的任意一天，比如 20210604

2021-06-04 10:06

liu.yang 回复 ZAW(提问者) ${time} 是你想查询的日期，可以用日期控件传参的

2021-06-04 10:07

还有3条评论点击查看

Z4u3z1Lv6专家互助
发布于2021-6-4 09:52

https://blog.csdn.net/weixin_41287692/article/details/80577828

举报收起评论(7)

ZAW(提问者) 可以写一下不

2021-06-04 09:53

Z4u3z1 回复 ZAW(提问者) oracle不熟悉大致应该是 SELECT A.*,lag(A.inqty, 1, null) over (order by A.CREDATE ASC,A.yearns DESC) as 去年同期 FROM ( select to_char(CREDATE,\'mm\') CREDATE,to_char(CREDATE,\'yyyy-mm\') yearn,to_char(CREDATE,\'yyyy\') yearns,nvl(sum(inqty),0)inqty,nvl(sum(OUTQTY),0) OUTQTY from BMS_ST_IO_UNION_V group by to_char(CREDATE,\'mm\'),to_char(CREDATE,\'yyyy\'),to_char(CREDATE,\'yyyy-mm\') ) A

2021-06-04 09:59