MySQL查缺补漏

rank类函数

概念

  1. rank() over
    作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。
    说明：例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次。即：1 1 3 4 5 5 7

  2. dense_rank() over
    作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。
    说明：和rank() over 的作用相同，区别在于dense_rank() over 排名是密集连续的。例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学接着下一个名次。即：1 1 2 3 4 5 5 6

  3. row_number() over
    作用：查出指定条件后的进行排名，条件相同排名也不相同，排名间断不连续。
    说明：这个函数不需要考虑是否并列，即使根据条件查询出来的数值相同也会进行连续排序。即：1 2 3 4 5 6

使用小提示

  • dense_rank() over 后面跟排序的依据的列，下面是用了一个排序好的列(order by score desc)。
  • 注意：如果select中有一列是用rank()这类函数，其他的列都会按着他这列规定好的顺序排。

代码

1
2
3
# Write your MySQL query statement below
select score, dense_rank() over (order by score desc) as 'rank'  #这个rank之所以要加引号，因为rank本身是个函数，直接写rank会报错
from scores;

实例

LeetCode 178. 分数排名

1
2
3
4
5
# Write your MySQL query statement below
SELECT
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS `rank`
FROM Scores

查重

方法

SQL：方法一

1
select email from person group by email having count(email) > 1;

解析
通过 group by 对 email 分组，在使用 having 将重复的 email 筛选出来。

SQL：方法二

1
2
3
select t.email from (
	select email, count(email) num from person group by email
) t where t.num > 1;

解析
将 email 分组后计算出相同 email 的数量作为临时表，筛选出 num > 1 的邮箱

SQL：方法三

1
2
3
4
with temp as (
	select email, count(email) num from person group by email
) 
select email from temp where num > 1;

解析
使用 with 建立临时表，和方法二一样

SQL：方法四

1
2
3
select distinct person.email from person
left join person temp on person.email = temp.email
where person.id != temp.id;

解析
使用 left join 自连，连接条件是 person.eamil = temp.email 并且通过 where 把 person.id != temp.id筛选出来，最后再通过 distinct 去重

实例

LeetCode 182. 查找重复的电子邮箱

1
SELECT Email AS Email FROM Person GROUP BY Email HAVING count(Email) > 1

LeetCode 596. 超过5名学生的课

Solution1: Group-By以后，直接对每一组判断count是否满足条件，注意count的对象是需要DISTINCT的。

1
2
3
4
SELECT class
FROM Courses
GROUP BY class
HAVING count(DISTINCT student) >= 5

Solution2：子查询，先得到【科目-选课人数】表，然后根据选课人数筛选从而得到符合条件的科目。

注意临时的表要起名字，AS xxx，同时，子查询中的变量最好起别名。

1
2
3
4
5
6
7
8
9
10
11
12
SELECT class
FROM
    (
        SELECT
            class,
            count(DISTINCT student) AS cs
        FROM
            Courses
        GROUP BY 
            class
    ) AS temp_table
WHERE cs >= 5

LEFT JOIN + WHERE

LeetCode 183. 从不订购的客户

1
2
3
4
5
SELECT c.Name AS Customers
FROM Customers c
LEFT JOIN Orders o
    ON c.Id = o.CustomerId
    WHERE o.Id IS NULL

JOIN + SUBQUERY

LeetCode 184. 部门工资最高的员工

1
2
3
4
5
6
7
8
9
10
11
12
13
# 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
    AND (e.departmentId, e.salary) IN (
        SELECT f.departmentId, max(f.salary)
        FROM Employee f
        GROUP BY f.departmentId
    )

DELETE

LeetCode 196. 删除重复的电子邮件

题解：https://leetcode.cn/problems/delete-duplicate-emails/solution/dui-guan-fang-ti-jie-zhong-delete-he-de-jie-shi-by/

显式写法

1
2
3
4
DELETE p1 FROM Person p1
JOIN Person p2
    ON p1.email = p2.email
    AND p1.id > p2.id

隐式写法

1
2
3
4
5
6
7
DELETE p1
FROM
    Person p1,
    Person p2
WHERE
    p1.email = p2.email
    AND p1.id > p2.id

IFNULL

概念

IFNULL(value1, value2)

如果value1是NULL，则将NULL转换为value2，并返回value2这个值。

实例

LeetCode 584. 寻找用户推荐人

1
2
3
4
# Write your MySQL query statement below
SELECT name
FROM customer
WHERE IFNULL(referee_id, 0) != 2

日期相关

方法

DATEDIFF(startDate, endDate) 返回值：endDate - startDate

实例

LeetCode 197. 上升的温度

1
2
3
4
5
6
# Write your MySQL query statement below
SELECT w1.id
FROM Weather w1
JOIN Weather w2
    ON w1.temperature > w2.temperature
    AND DATEDIFF(w1.recordDate, w2.recordDate) = 1