sql 求帮忙
id score
1 55
1 65
1 80
2 60
2 55
2 88
如何查询出来是
id s1 s2 s3
1 55 65 80
2 60 55 88

[解决办法]

SQL code

--我的有,等高人你的吧with t(id,score) as(select 1,55 from dualunion all select 1,65 from dualunion all select 1,80 from dualunion all select 2,60 from dualunion all select 2,65 from dualunion all select 2,88 from dual)select    id,    max(decode(rn,1,score,0)) s1,    max(decode(rn,2,score,0)) s2,    max(decode(rn,3,score,0)) s3from (select row_number() over(partition by id order by id) rn,id,score from t) group by id;/*        ID         S1         S2         S3                                     ---------- ---------- ---------- ----------                                              1         55         65         80                                              2         60         65         88   */
[解决办法]
with t(id,score) as(
select 1,55 from dual
union all select 1,65 from dual
union all select 1,80 from dual
union all select 2,60 from dual
union all select 2,65 from dual
union all select 2,88 from dual
)
SELECT * FROM (select id as fid,score,row_number()over(partition by id order by id) as rn from t ) t1
pivot(SUM(score) FOR rn IN (1,2,3))


FID                    1                      2                      3                     
---------------------- ---------------------- ---------------------- ----------------------  
1                      55                     65                     80                    
2                      60                     65                     88