-- -- -- -- -- 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;
分享到:
相关推荐
关于sql的20个测试题
适合初学者使用,它比较的基础,有助于你打牢基础,如果你能把它做完,那你就基本掌握了查询的相关知识!
javascript测验 一个用 javascript 编码的 javascript 测验
ssd3 practical quiz 5ssd3 practical quiz 5ssd3 practical quiz 5ssd3 practical quiz 5ssd3 practical quiz 5ssd3 practical quiz 5ssd3 practical quiz 5
ssd3 Practical Quiz 7 答案 ssd3 Practical Quiz 7 答案
SSD7 全部QUIZ答案,包含卡耐基梅隆网站上的全部quiz答案。想要选择题答案的看过来
https://www.w3schools.com/sql/sql_quiz.asp: Don't forget to take the quiz. Check out the amazing reference , may not be the full list, but good enough https://www.w3schools.com/sql/sql_ref_mysql.asp ...
第一章第二章2.5 重要(可能涉及一些计算)2.1 较重要(振幅//相位//频率//quiz 考过的香农·尼克斯特公式)2.2 2.3 2.6 简单了解2.4
卡耐基教程SSD3 quiz3的答案 卡耐基教程SSD3 quiz3的答案
ssd3 practical quiz 3ssd3 practical quiz 3ssd3 practical quiz 3ssd3 practical quiz 3ssd3 practical quiz 3ssd3 practical quiz 3ssd3 practical quiz 3
Best of Ruby Quiz
ssd3 practical quiz 7ssd3 practical quiz 7ssd3 practical quiz 7ssd3 practical quiz 7
ssd3 practical quiz 5ssd3 practical quiz 5ssd3 practical quiz 5ssd3 practical quiz 5ssd3 practical quiz 5ssd3 practical quiz 5
ssd3 practical quiz 2ssd3 practical quiz 2ssd3 practical quiz 2ssd3 practical quiz 2ssd3 practical quiz 2ssd3 practical quiz 2ssd3 practical quiz 2ssd3 practical quiz 2
sql_quiz_pm
ssd3 practical quiz 8ssd3 practical quiz 8ssd3 practical quiz 8ssd3 practical quiz 8ssd3 practical quiz 8ssd3 practical quiz 8
ssd3 practical quiz 1ssd3 practical quiz 1ssd3 practical quiz 1ssd3 practical quiz 1ssd3 practical quiz 1 ssd3 practical quiz 1
ssd1-quiz 选择题答案 本人做的不一定满分
SSD4完整的Quiz答案,可做考试前的复习资料