【MySQL】冷门易忘易错函数和经典例题

例题"/>

【MySQL】冷门易忘易错函数和经典例题

目录

• 一、函数
• • 1. UNION ALL 以及 UNION
  • • UNION ALL
    • UNION
  • 2. group_concat
  • 3. 日期函数
  • • to_days 返回从0000年到现在的天数
    • adddate 将时间/日期间隔添加到日期，然后返回日期
    • DATE_SUB 从日期减去指定的时间间隔
    • TIMESTAMPDIFF 计算相差天 / 秒 / 小时 数
    • DATEDIFF 返回值是相差的天数
• 二、例题：
• • 1. 列转行
  • 2. 行转列
  • 3. 查找第N高的数据，没有则返回null
• 三、一些有点难度的题
• • case when / if + SUM()函数

1. MySQL 函数 网站

一、函数

1. UNION ALL 以及 UNION

union：对多个结果，去重+排序
union all：对多个结果，不去重，不排序

UNION ALL

• 584. 寻找用户推荐人
• 1965. 丢失信息的雇员

UNION

• 1965. 丢失信息的雇员

2. group_concat

• 1484. 按日期分组销售产品

3. 日期函数

to_days 返回从0000年到现在的天数

select to_days('2015-01-04');  # 735967

adddate 将时间/日期间隔添加到日期，然后返回日期

select adddate("2015-01-03",INTERVAL 1 day) #2015-01-04

DATE_SUB 从日期减去指定的时间间隔

select DATE_SUB("2008-12-29",INTERVAL 2 DAY) #2008-12-27

• 197. 上升的温度

TIMESTAMPDIFF 计算相差天 / 秒 / 小时 数

#计算相差天数：
select TIMESTAMPDIFF(DAY,'2019-05-20', '2019-05-21'); # 1#计算相差小时数：
select TIMESTAMPDIFF(HOUR, '2015-03-22 07:00:00', '2015-03-22 18:00:00'); # 11#计算相差秒数：
select TIMESTAMPDIFF(SECOND, '2015-03-22 07:00:00', '2015-03-22 7:01:01'); # 61

• 197. 上升的温度

DATEDIFF 返回值是相差的天数

# 计算两个日期之间的天数。 (日期 1 - 日期 2)
select DATEDIFF('2007-12-31','2007-12-30');   # 1
select DATEDIFF('2010-12-30','2010-12-31');   # -1

• 197. 上升的温度

二、例题：

1. 列转行

• LeetCode_1795. 每个产品在不同商店的价格

2. 行转列

• LeetCode_1873. 计算特殊奖金
• LeetCode_627. 变更性别
• LeetCode_608. 树节点

关于 case when，可以看看这篇：【MySQL】SQL之CASE WHEN用法详解

3. 查找第N高的数据，没有则返回null

• LeetCode_176. 第二高的薪水

引申一下，如果获取第N高的数据呢？没有则返回null

Create table If Not Exists Employee (id int, salary int)
Truncate table Employee
insert into Employee (id, salary) values ('1', '100')
insert into Employee (id, salary) values ('2', '200')
insert into Employee (id, salary) values ('3', '300')

LIMIT + OFFSET （取第N高的数） + 子查询 （没有则返回null）

# 2. 挑出 Salary 列，显示的名字为 SecondHighestSalary
# 这里为什么要去重？是因为可能最高分有多个
SELECT DISTINCTSalary AS SecondHighestSalary
# 1. 找到 Employee 表
FROMEmployee
# 3. 把表中的数据按照薪水排序，最高的在最上面
# ！！！！！ 第 N 低的数据呢？asc 排序，offset N  ！！！！！！！！
ORDER BY Salary DESC
# 4. limit 1 ：取最高薪水
LIMIT 1 
# 5. 偏移 N 位，就获取到了第 N 高的数据了
OFFSET 1

此时，如果没有这样的第二最高工资，这个解决方案将被判断为 “错误答案”，因为本表可能只有一项记录。为了克服这个问题，我们可以将其作为临时表。

SELECT(SELECT DISTINCTSalaryFROMEmployeeORDER BY Salary DESCLIMIT 1 OFFSET 1) AS SecondHighestSalary;

LIMIT + OFFSET （取第N高的数） + IFNULL （没有则返回null）

IFNULL(expr1,expr2)的用法：

假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。

IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。

SELECTIFNULL((SELECT DISTINCT SalaryFROM EmployeeORDER BY Salary DESCLIMIT 1 OFFSET 1),NULL) AS SecondHighestSalary;

在前面的基础上，猜一猜 limit 3 offset 1 表示的是什么数据？

1、当 limit后面跟一个参数的时候，该参数表示要取的数据的数量

例如 select* from user limit 3 表示直接取前三条数据

2、当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量,例如

select * from user limit 1,3;

就是跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据

3、当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量 。

例如select * from user limit 3 offset 1;表示跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据

三、一些有点难度的题

case when / if + SUM()函数

• 1393. 股票的资本损益