[leetcode]database_SQL

175. Combine Two Tables

SELECT FirstName, LastName, City, StateFROM PersonLEFT JOIN AddressON Person.PersonId = Address.PersonId;

176. Second Highest Salary

select (  select distinct Salary from Salary Desc   limit 1 offset 1)as SecondHighestSalary

177. Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGINDECLARE M INT;SET M=N-1;  RETURN (      # Write your MySQL query statement below.      SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1  );END

• 注意:
  ① select * from table limit 2,1;
  //含义是跳过2条取出1条数据，limit后面是从第2条开始读，读取1条信息，即读取第3条数据
  ② select * from table limit 2 offset 1;
  //含义是从第1条（不包括）数据开始取出2条数据，limit后面跟的是2条数据，offset后面是从第1条开始读取，即读取第2,3条

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;

182. Duplicate Emails

select Emailfrom Persongroup by Emailhaving count(*) > 1

197. Rising Temperature

SELECT wt1.Id FROM Weather wt1, Weather wt2WHERE wt1.Temperature > wt2.Temperature AND       TO_DAYS(wt1.DATE)-TO_DAYS(wt2.DATE)=1;

181. Employees Earning More Than Their Managers

select E1.Name from Employee as E1, Employee as E2 where E1.ManagerId = E2.Id and E1.Salary > E2.Salary

183. Customers Who Never Order

SELECT A.Name from Customers AWHERE A.Id NOT IN (SELECT B.CustomerId from Orders B)