leetcode上数据库的题目（1）

175. Combine Two Tables

Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId is the primary key column for this table.

Table: Address

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

FirstName, LastName, City, State

Subscribe to see which companies asked this question.

将两张表通过PersonId关联起来，简单的使用左联结可破之。

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

176. Second Highest Salary

Write a SQL query to get the second highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return null.

Subscribe to see which companies asked this question.

找出Employee表中Salary第二高的人，如果没有的话得到的为null。原本的想法是用limit，但是答案错误，因为使用limit返回的是空集，不是null。所以要用另一种方法（这是因为经过了函数后空集会变成null？）：

SELECT MAX(Salary) AS SecondHighestSalary FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee);

177. Nth Highest Salary

Write a SQL query to get the nth highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

Subscribe to see which companies asked this question.

写一个函数，找出Employee表中Salary第M高的人，这里和上面那题原本的想法是一样的，注意的是第M个，索引应该是M-1：

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 FROM Employee ORDER BY Salary DESC LIMIT M, 1
  );
END

178. Rank Scores

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes" between ranks.

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

For example, given the above Scores table, your query should generate the following report (order by highest score):

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

Subscribe to see which companies asked this question.

对Scores表进行排序，并且在第二个字段Rank中标明排名。这里使用了子查询计算排名。

SELECT Score,(SELECT count(DISTINCT Score)+1 FROM Scores AS SB where SB.Score > SA.Score) AS Rank FROM Scores AS SA ORDER BY Score DESC;

180. Consecutive Numbers

Write a SQL query to find all numbers that appear at least three times consecutively.

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

Subscribe to see which companies asked this question.

找出Logs表中连续出现三次的数，一开始是直接用粗暴的方法，自联结三次，然后找出连续三个的是否是一样的：

SELECT DISTINCT l1.Num AS ConsecutiveNums FROM Logs AS l1, Logs AS l2, Logs AS l3 where l1.id = l2.id-1 and l2.id = l3.id-1 and l1.Num = l2.Num and l2.Num = l3.Num;

然后想到了可扩展的问题，下次四次五次就糟糕了，所以参考了一下讨论：

SELECT DISTINCT Num AS ConsecutiveNums FROM (
  SELECT Num, 
  CASE 
    WHEN @prev = Num then @count := @count+1
    WHEN @prev <> @prev := Num THEN @count := 1
  END AS n
  FROM Logs, (SELECT @count := 0, @prev := (SELECT Num FROM Logs LIMIT 0,1)) AS r
) AS a WHERE a.n >= 3;

601. Human Traffic of Stadium

Description Hints Submissions Solutions

• Total Accepted: 198
• Total Submissions: 608
• Difficulty: Hard
• Contributors: Douglas1612

X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, date, people

Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).

For example, the table stadium:

+------+------------+-----------+
| id   | date       | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+

For the sample data above, the output is:

+------+------------+-----------+
| id   | date       | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+

Note:
Each day only have one row record, and the dates are increasing with id increasing.

Subscribe to see which companies asked this question.

找出人数连续三次（或以上）超过100的记录。用自联结三张表的方法暴力解决：

select distinct s1.id, s1.date, s1.people from stadium as s1, stadium as s2, stadium as s3 where (s1.people >= 100 and s2.people >= 100 and s3.people >= 100) and ((s1.id = s2.id-1 and s2.id = s3.id-1) or (s2.id = s1.id-1 and s1.id = s3.id-1) or (s2.id = s3.id-1 and s3.id = s1.id-1)) order by s1.id;