请教高手们指点下小弟:
现有张表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)Oracle
现有张表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)Oracle
解决方案 »
- 500W条数据 快速插入,快速查询。
- 向oracle中插入一条数据,如何获取器序列号?
- oracle向数据库插入数据报错unique constraint (AD.PK_CA_NOTIF_TRACK) violated
- 包体编译总不通过,想请教下大家
- CRS启动问题(求助)
- 请问什么是oracle中序列(号)?
- 不错 取数据的问题要多少分就多少分
- 请问表名除了放在sys.obj$中,还有哪个系统表放有??
- 用 blob 字段的数据表应该注意什么,我现在向该字段插入一个 50M 的 Word 文档就报错,说“无法通过128(在表空间STAM中)扩展”
- 大G们,我遇到了个存储过程的问题,今天就要交任务了,救救小M啊,在线急等
- 如何根据查询的结果集来批量更新一张表
- oracle 如何实现某个用户只能查询某条数据
利用关键字LEAD与LAG取上记录的上一行或下一行记录,参考SUM() OVER()分析函数
请高手帮忙写下吧! 我实属不会LEAD与LAG。
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)
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 = '网销' )
--