PL/SQL Quiz

 

-- -- -- -- -- row_number rank dense_rank  3者区别-- -- -- -- -- 

 

-- Question_1: 男女成绩排行榜 http://www.iteye.com/topic/1029921

-- 不涉及并列排名(前3名)

 

select *
  from (select id
              ,name
              ,gender
              ,grade
              ,row_number() over(partition by gender order by grade desc) row_number
          from t_stu)
 where row_number <= 3;

 

 

 

-- 涉及并列排名(并列排名后的下一记录不连续)

-- 1, 2, 2, 4

 

select id
      ,name
      ,gender
      ,grade
      ,rank() over(partition by gender order by grade desc) rank
  from t_stu;

 

 

 

-- 涉及并列排名(并列排名后的下一记录连续)

-- 1, 2, 2, 3

 

select id
      ,name
      ,gender
      ,grade
      ,dense_rank() over(partition by gender order by grade desc) dense_rank
  from t_stu;

 

 

-- -- -- -- -- connect by [prior] start with -- -- -- -- --

select * from t_dept tb;

 

-- Question_2: 部门2及其所有下级部门

 

select lpad(' ', 2 * (level - 1), ' ') || dept_id as dept_id
      ,parent_id
      ,dept_name
      ,amount
  from t_dept
connect by parent_id = prior dept_id -- 找出所有PARENT_ID等于当前记录DEPT_ID的记录。
 start with dept_id = 2;

 

 

-- Question_3: 部门2及其所有上级部门

 

select lpad(' ', 2 * (level - 1), ' ') || dept_id as dept_id
      ,parent_id
      ,dept_name
      ,amount
  from t_dept
connect by prior parent_id = dept_id -- 左'上'右'下'
 start with dept_id = 2;

 

 

 

select lpad(' ', 2 * (level - 1), ' ') || dept_id as dept_id
      ,parent_id
      ,dept_name
      ,amount
  from t_dept
 start with dept_id = 1
connect by parent_id = prior dept_id
           and dept_id <> 3 -- 不包括部门3及其下属部门
;

 

 

 

select lpad(' ', 2 * (level - 1), ' ') || dept_id as dept_id
      ,parent_id
      ,dept_name
      ,amount
  from t_dept
 start with dept_id = 1
connect by parent_id = prior dept_id
 order siblings by amount asc; -- 注意 order siblings by 用法

 

-- -- -- -- -- 分页 -- -- -- -- --

 

select *
  from (select id
              ,name
              ,gender
              ,grade
              ,row_number() over(order by id) row_number
          from t_stu)
 where row_number between 5 and 10;

 

 

select *
  from (select rownum rn, tb.* from (select * from t_stu) tb)
 where rn between 5 and 10;

 

 

-- 性能高

 

select id, name, gender, grade
  from (select id, name, gender, grade, rownum rn
          from t_stu
         where rownum <= 10)
 where rn >= 5;