面试时常被问到的两条sql

一、oracle横列转换

----行列互换
select t.* from temp_sc t;
select sc.stdname, a.grade 语文, b.grade 数学, c.grade 物理, d.grade 化学
  from (select distinct stdname from temp_sc) sc,
       (select stdname, grade from temp_sc where stdsubject = '语文') a,
       (select stdname, grade from temp_sc where stdsubject = '数学') b,
       (select stdname, grade from temp_sc where stdsubject = '物理') c,
       (select stdname, grade from temp_sc where stdsubject = '化学') d
 where sc.stdname = a.stdname
   and sc.stdname = b.stdname
   and sc.stdname = c.stdname
   and sc.stdname = d.stdname;

二、树

--从哪条开始 start with
--连表条件 connect by
--往上还是往下 prior
--从哪条开始 start with
--连表条件 connect by
--往上还是往下 prior
select *
from tbl_tree
start with uuid = 1
connect by prior uuid = puuid
order by puuid