📯 SQL 语句 & 练习

1. 常见 sql 查询

1) 查询表中重复记录

SELECT
    *
FROM
    table-name
WHERE
    col-name IN (
        SELECT
            col-name
        FROM
            table-name
        GROUP BY
            col-name
        HAVING
            count(col-name) > 1
    )

2) 删除表中多余的重复记录

DELETE
FROM
    table-name
WHERE
    col-name IN (
         SELECT
            col-name
         FROM( // 更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式，因此再加了一层 select 封装
             SELECT
                col-name
            FROM
                table-name
            GROUP BY
                col-name
            HAVING
                count(col-name) > 1
        ) a
    )

3) 找出所有在 tableA 中存在，但 tableB 中不存在，且 tableC 中不存在的记录

# expect 自动消去重复行
select * from tableA execept (select * from tableB) except (select * from tableC);

---

2. SQL 练习

1) 🎌 175. 组合两个表

# Write your MySQL query statement below
select FirstName, LastName, City, State 
    from Person left join Address 
    on Person.personId = Address.personId

2) 🎌 🎌 176. 第二高的薪水

# Write your MySQL query statement below
# 若只有一行记录，没有外层 select 包含，则会报错
select (
    select distinct salary
    from Employee
    order by salary desc
    limit 1, 1
 ) as SecondHighestSalary 
    

3) 🎌 🎌 177. 第N高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set n = N - 1;
  RETURN (
      # Write your MySQL query statement below.
      select(
          select distinct salary from Employee
            order by salary desc
            limit n, 1
      ) 
  );
END

4) 🎌 🎌 🎌 178. 分数排名

# Write your MySQL query statement below
# score 列很简单就可以写出
# rank 列要求当前 score 的排名，
# 只需要记录有多少个分数比它高或者等于它，然后对记录进行去重，然后统计条数即是从 1 开始计算的排名
# rank 为关键字，需加引号
select a.score as score, 
    (select count(distinct b.score) from Scores b where b.score >= a.score) as `rank`
    from Scores a
    order by a.score desc

5) 🎌 🎌 🎌 180. 连续出现的数字

# Write your MySQL query statement below
# 三表内联
select distinct a.Num as ConsecutiveNums 
    from Logs a
    inner join Logs b
    inner join Logs c 
    on a.id = b.id - 1
    and b.id = c.id - 1
    and a.Num = b.Num
    and b.Num = c.Num

6) 🎌 181. 超过经理收入的员工

# Write your MySQL query statement below
select a.name as Employee from Employee a
    where a.managerId is not null 
    and a.salary > (select b.salary from Employee b where b.id = a.managerId);

7) 🎌 182. 查找重复的电子邮箱

# Write your MySQL query statement below
# 通过 group by + having 判断是否重复
select Email from Person
    group by Email
    having count(*) > 1;

8) 🎌 183. 从不订购的客户

# Write your MySQL query statement below
select c.Name as Customers from Customers c
    where (select count(o.Id) from Orders o where o.CustomerId = c.Id) = 0;

9) 🎌 🎌 184. 部门工资最高的员工

# Write your MySQL query statement below
# 左外连接，每组选取工资最高的一行
select d.name as Department, e.name as Employee, e.salary as Salary 
    from Employee e left join Department d 
    on e.departmentId = d.id 
    where (e.departmentId, e.salary) in (
        select departmentId, max(salary)
            from Employee
            group by departmentId
    )

10) 🎌 🎌 🎌 185. 部门工资前三高的所有员工

# Write your MySQL query statement below
# 左外连接
# 每组该员工，比该员工工资高的人不超过 3 个
select d.name as Department, e.name as Employee, e.salary as Salary 
    from Employee e left join Department d 
    on e.departmentId = d.id 
    where (
        select count(distinct e1.salary) 
            from Employee e1
            where e.departmentId = e1.departmentId 
            and e1.salary > e.salary
    ) < 3

11) 🎌 🎌 196. 删除重复的电子邮箱

# Please write a DELETE statement and DO NOT write a SELECT statement.
# Write your MySQL query statement below
delete p1 
    from Person p1, Person p2
    where p1.email = p2.email
    and p1.id > p2.id

12) 🎌 🎌 197. 上升的温度

# Write your MySQL query statement below
# 借助日期函数
select w1.id 
    from Weather w1 left join Weather w2
    on datediff(w1.recordDate, w2.recordDate) = 1
    where w1.Temperature > w2.Temperature;

13) 🎌 🎌 🎌 262. 行程和用户

# Write your MySQL query statement below
select t.request_at as Day, round(
    sum(if(t.status = 'completed', 0, 1))
    /
    count(t.status), 2
) as `Cancellation Rate`
from Trips t
    where t.client_id not in (
        select users_id
            from Users
        where users_id = t.client_id
        and banned = 'Yes'
    )
    and
    t.driver_id not in (
        select users_id
            from Users
        where users_id = t.driver_id
        and banned = 'Yes'
    )
    and request_at between '2013-10-01' and '2013-10-03'
    group by request_at