with b as (select a.id,a.name ,rank() over (partition by a.name order by a.id) rn from a) select min(case when rnn=1 then c.id else null end) N1, min(case when rnn=2 then c.id else null end) N2, min(case when rnn=3 then c.id else null end) N3 from (select b.id ,b.rn,row_number() over (partition by b.rn order by b.id) rnn from b)c group by c.rn order by c.rn试试这个
SELECT MAX(DECODE(COL2,'N1',COL1,NULL)) N1, MAX(DECODE(COL2,'N2',COL1,NULL)) N2, MAX(DECODE(COL2,'N3',COL1,NULL)) N3 FROM (SELECT A.*,ROW_NUMBER() OVER(PARTITION BY COL2 ORDER BY COL2) RN FROM TABS A) GROUP BY RN
用stored procedure我觉的没有那么复杂,也没必要用,总觉的用stored procedure有点大材小用,哈.
select min(case when rnn=1 then c.id else null end) N1,
min(case when rnn=2 then c.id else null end) N2,
min(case when rnn=3 then c.id else null end) N3
from (select b.id ,b.rn,row_number() over (partition by b.rn order by b.id) rnn from b)c group by c.rn order by c.rn试试这个
MAX(DECODE(COL2,'N2',COL1,NULL)) N2,
MAX(DECODE(COL2,'N3',COL1,NULL)) N3
FROM
(SELECT A.*,ROW_NUMBER() OVER(PARTITION BY COL2 ORDER BY COL2) RN
FROM TABS A)
GROUP BY RN