LeetCode sql 刷题

自身和自身比较

mysql> select * from salary;
+------+------+
| id   | sala |
+------+------+
|    1 |  200 |
|    2 |  300 |
|    3 |  400 |
|    4 |  500 |
+------+------+
4 rows in set (0.00 sec)

mysql> select a.id as a_id,a.sala as a_sala,b.id as b_id,b.sala as b_sala from salary a,salary b where a.sala>b.sala
    -> ;
+------+--------+------+--------+
| a_id | a_sala | b_id | b_sala |
+------+--------+------+--------+
|    2 |    300 |    1 |    200 |
|    3 |    400 |    1 |    200 |
|    4 |    500 |    1 |    200 |
|    3 |    400 |    2 |    300 |
|    4 |    500 |    2 |    300 |
|    4 |    500 |    3 |    400 |
+------+--------+------+--------+
6 rows in set (0.01 sec)

下面这个一直想不通，是否是b 表满足子查询总的b的条件

mysql> select b.* from salary b where 2 > (select count(distinct a.sala) from salary a where a.sala>b.sala)
    -> ;
+------+------+
| id   | sala |
+------+------+
|    3 |  400 |
|    4 |  500 |
+------+------+
2 rows in set (0.00 sec)

184. Department Highest Salar

问题：

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+
The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+
Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

解：可以使用多个条件进行一起过滤，但是e.DepartmentId, e.Salary这个顺序，要和in里面的字段的顺序要一样。

# Write your MySQL query statement below

select 
    d.Name as 'Department',
    e.Name as 'Employee',
    e.Salary as 'Salary'
from
Employee e
join 
Department d on e.DepartmentId = d.Id
where 
(e.DepartmentId, e.Salary) in
(
    select a.DepartmentId,
            max(a.Salary) 
    from Employee a
    group by a.DepartmentId
)
;

177. Nth Highest Salary

LIMIT 来定位数据。

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.

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

解：mysql还是要再看看。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    DECLARE M INT;
    set M = N-1;
  RETURN (
      # Write your MySQL query statement below.
      #初始记录行的偏移量是 0,第n个，就是包括第n个，那么索引就是N-1
     SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1
     
  );
END

601. Human Traffic of Stadium

难度很高的一道题

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       |
+------+------------+-----------+

解：几个点，首先考察如何判断三个连续的值，建立三个原表的引用，where三个引用，那么形成的是一个笛卡尔积。

select distinct t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
;

这个结果出来的是满足大于100的值。
Considering t1, t2 and t3 are identical, we can take one of them to consider what conditions we should add to filter the data and get the final result. Taking t1 for example, it could exist in the beginning of the consecutive 3 days, or the middle, or the last.

• t1 in the beginning: (t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) -- t1, t2, t3
• t1 in the middle: (t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
• t1 in the end: (t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1

select t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
      (t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1)  -- t1, t2, t3
    or
    (t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
    or
    (t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
)
;