17,088
社区成员
发帖
与我相关
我的任务
分享
/* Formatted on 2013/08/02 15:22 (Formatter Plus v4.8.7) */
SELECT curmonth "月份", ((curmonth_sum - lastyear_sum) / lastyear_sum * 100) "同比",
((curmonth_sum - lastmonth_sum) / lastmonth_sum * 100) "环比"
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
GROUP BY t.salarymonth)
/* Formatted on 2013/08/02 15:00 (Formatter Plus v4.8.7) */
SELECT t.salarymonth,
SUM (t.salary)
/ (SELECT SUM (t1.salary)
FROM d_month_salary t1
WHERE t1.salarymonth =
((SUBSTR (t.salarymonth, 1, 4) - 1)
|| SUBSTR (t.salarymonth, -2))) "同比",
SUM (t.salary)
/ (SELECT SUM (t1.salary)
FROM d_month_salary t1
WHERE t1.salarymonth =
TO_CHAR (add_months(TO_DATE (t.salarymonth, 'yyyymm'),-1),
'yyyymm')) "环比"
FROM d_month_salary t
GROUP BY t.salarymonth;
select t.*, t.rowid from D_MONTH_SALARY t;
select t.salarymonth,
sum(t.salary) / (select sum(t1.salary)
from D_MONTH_SALARY t1
where t1.salarymonth = (substr(t.salarymonth, 1, 4) - 1) ||
substr(t.salarymonth, -2)) "同比",
sum(t.salary) /
(select sum(t1.salary)
from D_MONTH_SALARY t1
where t1.salarymonth =
to_char(to_date(t.salarymonth, 'yyyymm') - 1, 'yyyymm')) "环比"
from D_MONTH_SALARY t
group by t.salarymonth;