leetcode Database复习2

180. Consecutive Numbers

https://leetcode.com/problems/consecutive-numbers/

考察两张表的联合，注意主键，非主键可以为NULL, 题目要求的是当address.personid为null时候person里面的信息仍然输出，因此如果用from table 1 table 2 where 1.a=2.b的形式遗失person里面的信息。

select FirstName, LastName,City,State
FROM
Person left join Address 
where Person.PersonId = Addree.PersonId

Person.PersonId为主键，非NULL，Address.PersonId不是主键，使用left join避免null的问题

使用using改进

<span style="font-family: Arial, Helvetica, sans-serif;">select FirstName, LastName,City,State</span>

FROM
Person left join Address 
using(PersonId)

176. Second Highest Salary

https://leetcode.com/problems/second-highest-salary/

select 
 IF(COUNT(A.Salary)>1,min(A.Salary),null)as SecondHighestSalary
 from
 (select distinct Salary from Employee order by Salary desc limit 0,2)A

先选出两个最大值作为临时表，然后考虑第二高可能为null, 用if表达式判断

select ifnull((select distinct Salary  from Employee order by Salary desc limit 1,1),null) as SecondHighestSalar

IFNULL(expr1,expr2)          
如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境

select max(Salary) AS SecondHighestSalary From Employee WHERE Salary not in (SELECT max(Salary) FROM Employee )

select max(Salary) AS SecondHighestSalary From Employee WHERE Salary <(SELECT max(Salary) FROM Employee )

利用max不存在return null的特点

177. Nth Highest Salary
https://leetcode.com/problems/nth-highest-salary/

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare M int;
set M=N-1;
  RETURN (
      # Write your MySQL query statement below.
      #select distinct Salary form Employee order by Salary desc limit N-1 1
      SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1
  );
END

limit m,n的使用过程中，m,n必须为常量，因此需要declare声明

178. Rank Scores

https://leetcode.com/problems/rank-scores/

select 
Score,
(select count(distinct Score)+1 from Scores where Score>s.Score) as Rank
from Scores s order by Score desc

比当前值大的全部累加和+1

181. Employees Earning More Than Their Managers
https://leetcode.com/problems/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.ManagerId is not null and a.Salary > b.Salary

left jion 用where筛选

182. Duplicate Emails

https://leetcode.com/problems/duplicate-emails/

select Email
 from Person 
 group by Email 
 having count(Id)>1

简单的having使用

 SELECT DISTINCT a.Email
 FROM Person a JOIN Person b
 ON (a.Email = b.Email)
 WHERE a.Id <> b.Id

两张表，b作为参考，distinct去重输出

 SELECT DISTINCT a.Email
 FROM Person a
 WHERE EXISTS(
     SELECT 1
     FROM Person b
     WHERE a.Email = b.Email
     LIMIT 1, 1
 )

limit 1.1查看b.email中有没有第二个相等的，然后select 1输出作为exists的判断

 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

minus的版本

184. Department Highest Salary

https://leetcode.com/problems/department-highest-salary/

SELECT D.Name AS Department ,E.Name AS Employee ,E.Salary 
FROM
	Employee E,
	(SELECT DepartmentId,max(Salary) as max FROM Employee GROUP BY DepartmentId) T,
	Department D
WHERE E.DepartmentId = T.DepartmentId 
  AND E.Salary = T.max
  AND E.DepartmentId = D.id

三张表where建立约束联合联合，table T是临时表