Question

求一条SQL语句！

Answer 1

经过测试：

select aa.job 代码,aa.sal 类别1,bb.sal 类别2,cc.sal 类别3,dd.sal 类别4 from
(select a.job,a.sal from (select job,sal,row_number() over(partition by job order by job) pm from emp) a where a.pm =1) aa
left outer join
(select b.job,b.sal from (select job,sal,row_number() over(partition by job order by job) pm from emp) b where b.pm =2) bb
on aa.job=bb.job
left outer join
(select c.job,c.sal from (select job,sal,row_number() over(partition by job order by job) pm from emp) c where c.pm =3) cc
 on bb.job=cc.job
left outer join
(select d.job,d.sal from (select job,sal,row_number() over(partition by job order by job) pm from emp) d where d.pm =4) dd
 on cc.job=dd.job

Answer 2

select a.代码,sum(decode(a.类别,'1',a.数量)) 类别1,sum(decode(a.类别,'2',a.数量)) 类别2,sum(decode(a.类别,'3',a.数量)) 类别3,sum(decode(a.类别,'41',a.数量)) 类别4 from T1 a group by a.代码 order by a.代码

PS:我的是oracle，别的没试过 如果是mysql用case一样的效果

mysql & oracle

select a.代码,sum(case a.类别 when '1' then a.数量 end) 类别1,sum(case a.类别 when '2' then a.数量 end) 类别2,sum(case a.类别 when '3' then a.数量 end) 类别3,sum(case a.类别 when '4' then a.数量 end) 类别4 from T1 a group by a.代码 order by a.代码

Answer 3

select 代码
,isNull(类别1,'') as 类别1
,isNull(类别2,'') as 类别2
,isNull(类别3,'') as 类别3
,isNull(类别4,'') as 类别4
from (
select 代码
,类别=(case 类别 when 1 then '类别1' when 2 then '类别2' when 3 then '类别3' when 4 then '类别4' end)
,数量
from T1) T2
pivot(max(数量) for 类别 in([类别1],[类别2],[类别3],[类别4]))a