[leetcode] database解题记录

题目：左连接Person表和Address表。

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

7个case耗时1001ms（注意耗时多次提交会不同，一般相差不大，偶尔也有相差大的） --2015/1/11

176 Second Highest Salary

题目：写一条sql语句查询Employee 表中第二高工资值。

select max(Salary ) from Employee  where Salary  not in(select max(Salary ) from Employee)

7个case耗时1001ms --2015/1/11

177 Nth Highest Salary

查询第n大的值。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT IFNULL((SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M ,1), NULL)
  );
END
#14 cases 925ms

178 Rank Scores

按得分排名并计算排名。

select Scores.score,count(Ranking.Score) as rank from 
Scores,
(select distinct score from Scores) Ranking 
where Scores.score <= Ranking.Score 
group by Scores.id,Scores.score 
order by Scores.score desc;
#955ms

180 Consecutive Numbers

找出至少连续出现3次的Num。这道题确实不知道怎么下手，看了答案理解了一下。

select DISTINCT num FROM
(select num,
    case 
        when @record = num then @count:=@count+1
        when @record <> num then @count:=1 end as n
    from 
        Logs ,(select @count:=0,@record:=(SELECT num from Logs limit 0,1)) r
) a
where a.n>=3

这个居然也可以过?

select distinct(a.Num) from Logs a, Logs b,Logs c where a.Id=b.Id+1 and a.Num=b.Num and b.Id=c.Id+1 and b.Num=c.Num #21 cases 1577ms

181 Employees Earning More Than Their Managers

查询工资比上司高的员工。

select a.name as Employee from Employee a left join Employee b on a.ManagerId = b.Id where a.Salary>b.Salary #14 cases 1336ms

select a.Name from Employee a inner join Employee b on a.ManagerId=b.Id where a.Salary>b.Salary #用内联时间应该更短

182 Duplicate Emails

题目：查找表中有重复的emails。

这里提供四种方法：

select Email from Person group by Email having count(*) > 1;
#14 cases 1069ms
select distinct a.Email from Person a join Person b on (a.Email = b.Email) where a.Id <> b.Id;#14 cases 1048ms

select distinct a.Email from Person a where exists(select 1 from Person b where a.Email = b.Email limit 1, 1); #14 cases 1138ms

select distinct a.Email from Person a left join (select Id,Email from Person group by Email) b on (a.Email = b.Email) and (a.Id=b.Id) where b.Email is NULL;#14 cases 1060ms

183 Customers Who Never Order

题目：查出没有订单的客户。

select Name as Customers from Customers where Id not in(select distinct(CustomerId) from Orders); #693ms

184 Department Highest Salary

题目：查询每个部门最高的工资。

select D.Name as Department, E.Name as Employee, E.Salary as Salary from  Employee E ,Department D where  E.DepartmentId=D.Id AND (DepartmentId,Salary) in (SELECT DepartmentId,max(Salary) as max FROM Employee GROUP BY DepartmentId) ;#1736ms