一张表,但是需要根据选择的月份进行(最近3,6,9,12个月)统计和同比显示,想问问大神要怎么写
SELECT rq "月份", ((value - old_sum) / old_sum * 100||'%') "同比",value "总数" from
(
select to_char(y.crjsj,'YYYYMM') as rq,count(1) as value,(select count(1) as value from yw_t_jy y1 where to_char(y1.crjsj,'YYYYMM')>=to_char(add_months(TO_DATE (start, 'YYYYMM'),-12),'YYYYMM') and to_char(y1.crjsj,'YYYYMM')<=to_char(add_months(TO_DATE (end, 'yyyymm'),-12),'YYYYMM') and y1.fxqd=0
group by to_char(y1.crjsj,'YYYYMM')) old_sum from yw_t_jy y where to_char(y.crjsj,'YYYYMM')>=start and to_char(y.crjsj,'YYYYMM')<=end and y.fxqd=0
group by to_char(y.crjsj,'YYYYMM') order by rq
)
这是我写的,如果只有一个月还能计算,但是多个月就没办法弄了,一直提示单个子查询返回多个行
SELECT rq "月份", ((value - old_sum) / old_sum * 100||'%') "同比",value "总数" from
(
select to_char(y.crjsj,'YYYYMM') as rq,count(1) as value,(select count(1) as value from yw_t_jy y1 where to_char(y1.crjsj,'YYYYMM')>=to_char(add_months(TO_DATE (start, 'YYYYMM'),-12),'YYYYMM') and to_char(y1.crjsj,'YYYYMM')<=to_char(add_months(TO_DATE (end, 'yyyymm'),-12),'YYYYMM') and y1.fxqd=0
group by to_char(y1.crjsj,'YYYYMM')) old_sum from yw_t_jy y where to_char(y.crjsj,'YYYYMM')>=start and to_char(y.crjsj,'YYYYMM')<=end and y.fxqd=0
group by to_char(y.crjsj,'YYYYMM') order by rq
)
这是我写的,如果只有一个月还能计算,但是多个月就没办法弄了,一直提示单个子查询返回多个行
解决方案 »
- oracle取出最后10条记录 怎麼寫
- ask lpc19598188
- 关于Oracle 触发器的问题
- 读入的数据被截断!
- 求年龄
- 求一条 SQL 语句,谢谢
- 简单的问题!查询日期为20050511的记录,where riqi=日期,这个sql语句怎么表达!
- 有没有这样的SQL语句
- 求一句sql语句?name字段中如果没有‘///’字符,就把name字段中的内容末尾,去掉空格,然后加上“///”
- oracle是否可以实现自动发Mail,可以的话,怎么实现?
- 存储过程实现csv文件导入oracle时,utl_file.fopen限制32767
- 请教sql语句,如何用like关联两个表中的字段,将查询结果的部分内容提取出来?
select count(1) as value from yw_t_jy y1 where to_char(y1.crjsj,'YYYYMM')>=to_char(add_months(TO_DATE (start, 'YYYYMM'),-12),'YYYYMM') and to_char(y1.crjsj,'YYYYMM')<=to_char(add_months(TO_DATE (end, 'yyyymm'),-12),'YYYYMM') and y1.fxqd=0
group by to_char(y1.crjsj,'YYYYMM') 这里会返回多个结果集,
环比应该可以用add_months(-12)实现
同比应该可以用lag(1)取前一行实现