WITH T AS (
SELECT B.*,ROW_NUMBER()OVER(PARTITION BY ID,ORDER BY JOB) RN
FROM B
)
SELECT A.ID,MAX(A.NAME) NAME,
MAX(DECODE(B.RN,1,B.JOB)) JOB1,
MAX(DECODE(B.RN,1,B.salary)) salary1,
MAX(DECODE(B.RN,2,B.JOB)) JOB2,
MAX(DECODE(B.RN,2,B.salary)) salary2
FROM A,B
WHERE A.ID=B.ID
GROUP BY A.ID
SELECT B.*,ROW_NUMBER()OVER(PARTITION BY ID,ORDER BY JOB) RN
FROM B
)
SELECT A.ID,MAX(A.NAME) NAME,
MAX(DECODE(B.RN,1,B.JOB)) JOB1,
MAX(DECODE(B.RN,1,B.salary)) salary1,
MAX(DECODE(B.RN,2,B.JOB)) JOB2,
MAX(DECODE(B.RN,2,B.salary)) salary2
FROM A,B
WHERE A.ID=B.ID
GROUP BY A.ID
仅供参考:select t1.id,
t1.name,
(select min(t2.job)
from (select b.*,
dense_rank() over(partition by id order by job) rn
from b) t2
where t2.rn = 1
and t2.id = t1.id) job1,
(select sum(t2.salary)
from (select b.*,
dense_rank() over(partition by id order by job) rn
from b) t2
where t2.rn = 1
and t2.id = t1.id) salary1,
(select min(t2.job)
from (select b.*,
dense_rank() over(partition by id order by job) rn
from b) t2
where t2.rn = 2
and t2.id = t1.id) job2,
(select sum(t2.salary)
from (select b.*,
dense_rank() over(partition by id order by job) rn
from b) t2
where t2.rn = 2
and t2.id = t1.id) salary2
from a t1;