如下左表T 写一个存储过程 要求deptno列不变 重新排序salary列 salary对应最大的值 要和deptno最大的值在同一行上
结果如图右表 DEPTNO SALARY DEPTNO SALARY
------ ------ -------- --------
D 8000 D 5000
A 5000 A 8000
B 4000 B 7000
C 7000 C 6000
E 6000 E 4000
结果如图右表 DEPTNO SALARY DEPTNO SALARY
------ ------ -------- --------
D 8000 D 5000
A 5000 A 8000
B 4000 B 7000
C 7000 C 6000
E 6000 E 4000
WITH t1 AS (
SELECT 'D' AS deptno,8000 AS SALARY FROM dual
UNION ALL
SELECT 'A' AS deptno,5000 AS SALARY FROM dual
UNION ALL
SELECT 'B' AS deptno,4000 AS SALARY FROM dual
UNION ALL
SELECT 'C' AS deptno,7000 AS SALARY FROM dual
UNION ALL
SELECT 'E' AS deptno,6000 AS SALARY FROM dual
)SELECT deptno,salary FROM
(SELECT salary,RANK()OVER(ORDER BY salary DESC) as r1 FROM t1) a,
(SELECT deptno,RANK()OVER(ORDER BY deptno ) as r2 FROM t1 ) b
WHERE a.r1=b.r2
好像还有一些其他方法,可以再试试。
一定要procedure 吗? 我觉得 能一条句子写出来的 就不要用PLSQL。你要的话,我也可以写写看。
create table TTestTbl as
select 1 as deptno,8000 as salary from dual
union select 2 ,5220 from dual
union select 3 , 8555 from dual
union select 4 ,3322 from dual
select a.id,sum(a.deptno) bb ,sum(b.salary) aa from (
select rownum id,a.deptno,a.salary from (select deptno,0 as salary from ttesttbl order by deptno desc) a ) a,
(select rownum as ID,deptno,salary from (select 0 as deptno ,salary from ttesttbl order by salary desc)) b
where a.id=b.id
group by a.id
order by a.id