SQL(三)

180. 连续出现的数字

暴力三表连接

# Write your MySQL query statement below
SELECT DISTINCT(l1.Num) AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num

# Write your MySQL query statement below
SELECT e1.Name AS Employee
FROM Employee AS e1 INNER JOIN Employee AS e2
ON e1.ManagerId = e2.ID
WHERE e1.Salary > e2.Salary

# Write your MySQL query statement below
SELECT Email
FROM
(SELECT Email, COUNT(1) AS times
FROM Person
GROUP BY Email) AS a
WHERE times > 1

# Write your MySQL query statement below
SELECT c.Name AS Customers
FROM Customers AS c LEFT JOIN Orders AS o
ON c.ID = o.CustomerId
WHERE o.ID IS NULL

解法一 IN关键字

SELECT
	Department.NAME AS Department,
	Employee.NAME AS Employee,
	Salary 
FROM
	Employee,
	Department 
WHERE
	Employee.DepartmentId = Department.Id 
	AND ( Employee.DepartmentId, Salary ) 
    IN (SELECT DepartmentId, max( Salary ) 
        FROM Employee 
        GROUP BY DepartmentId )

解法二，与子表连接

# Write your MySQL query statement below
SELECT a.Department, Employee.Name AS Employee, Salary
FROM  Employee LEFT JOIN (SELECT Department.Id AS new_id, Department.Name AS Department, MAX(Employee.Salary) AS max_salary
FROM Employee INNER JOIN Department
ON Employee.DepartmentId = Department.Id
GROUP BY Employee.DepartmentId) AS a
ON Employee.DepartmentId = a.new_id
WHERE Salary = max_salary