请教高手们指点下小弟:
现有张表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
解决方案 »
- oracle数据库怎么用?
- 安装oracle遇到问题,请指教
- 关于索引使用的问题
- 查询求教
- 求和问题
- 在linux使用userdel oracle将oracle用户删除了导致oracle不能用了,如何解决,分数不是问题
- 新手请教:为什么我的OMS里没有“数据库”这个结点?
- SQL用户提问:高分寻求ORACLE学习的方法!!!曾经是SQL用户的ORACLE高手请进-----急急急急急
- 安装oracle 9i 时出现tns 包写入失败错误,请帮忙!今天解决200分!明天解决100分!:)
- 请教一个sql,求大神帮助
- 如何根据查询的结果集来批量更新一张表
- 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 = '网销' )
--