刷leetcode的第4天

1.分数排名

【题目】

【思路1：sql sever】

select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as 'Rank'
from Scores a
order by a.Score DESC

【思路2】

select score,
(dense_rank() over (order by Score desc)) AS "rank" 
from Scores 

本来以为第二种会快一些，结果还慢一些

2.连续出现的数字

【题目】

 

【思路1：sql sever】

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
;

【思路2：mysql】

select distinct num as ConsecutiveNums 
from (select num, 
             lag(num, 1, null) over (order by id) lag_num, 
             lead(num, 1, null) over (order by id) lead_num
      from logs) l
where l.Num = l.lag_num
  and l.Num = l.lead_num

第二种要快一些