178.分数排名
题目:
select Score,dense_rank() over(order by Score desc) Rank from Scores;
dense_rank()函数,oracle分级函数。讲解参考:oracle分级函数
180.连续出现的数字
题目:
select distinct num as ConsecutiveNums
from(
select num,id,row_number() over(partition by num order by id) as rn
from logs
)
group by num,id-rn
having count(1)>=3
row_number()函数、partition by。这道题一开始审题错误,看做计数了。重点是连续。这里用函数看起来比较简洁。
181.超过经理收入的员工
题目:
select a.name as employee from employee a,employee b where a.ManagerId = b.Id and a.salary>b.salary;
连接查询,将一个表看做两个表,分别为员工表和经理表
182.查找重复的电子邮箱
题目:
select Email from Person group by Email having count(1) >= 2
数据库分组
183.从不订购的客户
题目:
select Name as Customers from Customers a where a.Id not in (
select CustomerId from Orders
)
not in考查