新手求教oracle同比和环比sql语句
数据表
create table d_month_salary(
id number(2) primary key,
salaryMonth varchar2(10),
salary number(10));
查询数据如下:
请高手们指点指点,感激不尽撒... Oracle
[解决办法]
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;
/* 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;