SQL(四）

# Write your MySQL query statement below
SELECT Department.Name AS Department, e1.Name AS Employee, Salary
FROM Employee e1 INNER JOIN Department
ON e1.DepartmentId = Department.Id
where 3 >
(
select count(distinct e2.Salary)
from Employee e2
where e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId
)

按照group by删除

delete from Person
where Id not in 
(
    select need.id from 
    (
        select min(Id) as id from Person  group by Email
    ) as need 
)

分组的时候统计最小的ID，方便之后求出不在最小的ID的范围中的id;

表连接删除

DELETE
FROM Person
WHERE Id in 
(SELECT * FROM
(SELECT p1.Id
FROM Person AS p1 INNER JOIN Person AS p2
ON p1.Email = p2.Email
WHERE p1.Id > p2.Id) t1)

注意这里需要再加一个select * from,以为更改表不能直接的从select的结果更新表，所以需要再加一个select

# Write your MySQL query statement below
SELECT w2.id
FROM Weather w1 INNER JOIN Weather w2
WHERE datediff(w2.recordDate, w1.recordDate) = 1
AND w2.Temperature > w1.Temperature