LeetCode记录(SQL)(普通题)

记录在LeetCode的SQL相关问题的提交记录，因为都是一些简单问题，就不单独放了，全部归档在这篇里，均是用MySQL语法，如果用了其它的会注明

1.组合两个表

select Person.FirstName, Person.LastName, Address.City, Address.State
from Person
left join Address
on Person.PersonId = Address.PersonId

总结：left join的基本用法，应该是要考察几种join的区别，没啥好总结

2.第二高的薪水

select IFNULL((select distinct Salary
from Employee
order by Salary desc
limit 1,1),null) as SecondHighestSalary

因为SQL Server没有limit，这里用offset+fetch实现，另外注意在IFNULL在SQL Server等价的是ISNULL

select ISNULL((select distinct Salary
from Employee
order by Salary desc
offset 1 rows
fetch next 1 rows only),null) as SecondHighestSalary

总结：注意limit的位置在order by之后，以及注意审题即可

3.第N高的薪水

CREATE FUNCTION getNthHighestSalary(numN INT) RETURNS INT
BEGIN
        SET numN = numN - 1;
        RETURN(
                select IFNULL((select distinct Salary
                from Employee
                order by Salary desc
                limit numN,numN),null)
        );
END

SQL Server

CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
    SET @N = @N - 1;
    RETURN (
        select ISNULL((select distinct Salary
                from Employee
                order by Salary desc
                offset @N rows
                fetch next 1 rows only),null)      
    );
END

总结：和第二题相比，主要考察如何用函数创建自定义function

4.分数排名

MySQL

select Scores.Score,T2.Rank from Scores
left join 
(select T1.Score,Convert((@rownum := @rownum + 1),unsigned) as `Rank`
from 
(select distinct Score,
(SELECT @rownum := 0) from Scores order by Score desc)T1)T2 on Scores.Score = T2.Score
order by Score desc

SQL Server

select Scores.Score,T2.Rank from Scores
left join 
(select T1.Score,ROW_NUMBER() over(ORDER BY T1.Score desc) as Rank
from 
(select distinct Score from Scores)T1)T2 on Scores.Score = T2.Score
order by Score desc

总结：注意在MySQL中，用``符号来对保留字进行转义，另外注意取行号在两种数据库中的写法

PS:在版本允许的情况下，用函数DENSE_RANK()可以很容易处理这个问题，写法为

select Score,DENSE_RANK() OVER(order by Score desc) as `Rank` from Scores

5.连续出现的数字

MySQL

select distinct T1.Num as ConsecutiveNums from
(select case when  @preNum = Num then @times := @times+1
else
(case when (@preNum :=Num) is not null then @times :=1 end)
end as num_times,Num
from Logs,(select @preNum := 0,@times := 0)init)T1
where T1.num_times>2

总结：需要熟悉MySQL用户变量的用法，体会其初始化之后的作用域以及随着查询值变化的过程