Oracle中的环比和同比问题

[Drat殇]

请教高手们指点下小弟：
现有张表d_month_salary结构和数据如下：

当要查询'网销','店铺'月份的环比和同比，以下语句怎么修改才能互不干扰
SELECT curmonth month, ((curmonth_sum - lastyear_sum) / lastyear_sum * 100) identical,((curmonth_sum - lastmonth_sum) / lastmonth_sum * 100) annulus
FROM (SELECT t.salarymonth curmonth,
SUM (t.salary) curmonth_sum,
(SELECT SUM (t1.salary) FROM d_month_salary t1 WHERE t1.salarymonth =((SUBSTR (t.salarymonth, 1, 4) - 1)|| SUBSTR (t.salarymonth, -2))) lastyear_sum,
(SELECT SUM (t1.salary) FROM d_month_salary t1 WHERE t1.salarymonth =TO_CHAR(ADD_MONTHS (TO_DATE (t.salarymonth, 'yyyymm'),-1),'yyyymm')) lastmonth_sum FROM d_month_salary t
WHERE indexname = ? //?='网销'或'店铺'
GROUP BY t.salarymonth
ORDER BY t.salarymonth)

[Drat殇]

identical（同比）,annulus(环比).

[why0302]

没仔细看你的SQL，不用写这么复杂的SQL，感觉SQL有问题，建议你利用oracle分析函数， 利用关键字LEAD与LAG取上记录的上一行或下一行记录，参考SUM() OVER()分析函数

[Drat殇]

怎么不见解答呢？ 我说的不清楚？

[Drat殇]

引用 4 楼 why0302 的回复:

没仔细看你的SQL，不用写这么复杂的SQL，感觉SQL有问题，建议你利用oracle分析函数， 利用关键字LEAD与LAG取上记录的上一行或下一行记录，参考SUM() OVER()分析函数

请高手帮忙写下吧！ 我实属不会LEAD与LAG。

[why0302]

根据你描述的业务，你的SQL那个取上一年同期与上一个月的子查询，应该加参数 SELECT curmonth MONTH, ((curmonth_sum - lastyear_sum) / lastyear_sum * 100) identical, ((curmonth_sum - lastmonth_sum) / lastmonth_sum * 100) annulus FROM (SELECT t.salarymonth curmonth, SUM (t.salary) curmonth_sum, (SELECT SUM (t1.salary) FROM d_month_salary t1 WHERE t1.salarymonth = ( (SUBSTR (t.salarymonth, 1, 4) - 1) || SUBSTR (t.salarymonth, -2) ) AND t1.indexname = '网销' ) lastyear_sum, (SELECT SUM (t1.salary) FROM d_month_salary t1 WHERE t1.salarymonth = TO_CHAR (ADD_MONTHS (TO_DATE (t.salarymonth, 'yyyymm'), -1 ), 'yyyymm' ) AND t1.indexname = '网销' ) lastmonth_sum FROM d_month_salary t WHERE indexname = '网销' GROUP BY t.salarymonth ORDER BY t.salarymonth)

[why0302]

oracle分析函数，前提是你的表中的数据是连续的，不能断月 --1.d_month_salary主键是 SALARYMONTH，INDEXNAME时 SELECT curmonth MONTH, ((curmonth_sum - lastyear_sum) / lastyear_sum * 100) identical, ((curmonth_sum - lastmonth_sum) / lastmonth_sum * 100) annulus FROM (select t.SALARYMONTH as curmonth ,t.SALARY as curmonth_sum ,LAG(t.SALARY, 12, NULL) OVER (ORDER BY t.SALARYMONTH) AS lastyear_sum ,LAG(t.SALARY, 1, NULL) OVER (ORDER BY t.SALARYMONTH) AS lastmonth_sum from d_month_salary t where t.INDEXNAME = '网销' ) --2 d_month_salary没有主键时 SELECT curmonth MONTH, ((curmonth_sum - lastyear_sum) / lastyear_sum * 100) identical, ((curmonth_sum - lastmonth_sum) / lastmonth_sum * 100) annulus, FROM (select t.SALARYMONTH as curmonth ,t.SALARY as curmonth_sum ,LAG(t.SALARY, 12, NULL) OVER (ORDER BY t.SALARYMONTH) AS lastyear_sum ,LAG(t.SALARY, 1, NULL) OVER (ORDER BY t.SALARYMONTH) AS lastmonth_sum from (select SALARYMONTH as SALARYMONTH ,INDEXNAME as INDEXNAME ,sum(SALARY) as SALARY from d_month_salary group by SALARYMONTH, INDEXNAME ) t where t.INDEXNAME = '网销' ) --

[why0302]

上面的第二个SQL，((curmonth_sum - lastmonth_sum) / lastmonth_sum * 100) annulus,后多了一个逗号