from -> where -> group by -> select -> order by -> limit
IF(表达式,表达式为真时取值,表达式为假时取值)
isnull(expr) 的用法: 如expr 为null,那么isnull() 的返回值为 1,否则返回值为 0
IFNULL(expr1,expr2)的用法:假如expr1 不为 NULL,则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。
limit y 分句表示: 读取 y 条数据
limit x, y 分句表示: 跳过 x 条数据,读取 y 条数据
limit y offset x 分句表示: 跳过 x 条数据,读取 y 条数据
limit n 等价于 limit 0,n
mysql字符串分割
1、left(str,n)//从左开始分割,截取n位
2、right(str,n)//从右开始分割,截取n位
3、substring(str,pos)//从哪一位开始
substring(str,pos,length)//从哪一位开始截取几位
4、substring_index(str,delim,count)//substring_index(被截取字段,分隔符,关键字出现的次数)
SQL51 查找字符串 10,A,B 中逗号,出现的次数cnt
# select char_length("10,A,B")
# -char_length(replace("10,A,B",",",""));
select (length("10,A,B") - length(replace("10,A,B",",","")) )
as cnt;
- rank函数
- dense_rank函数
- row_number函数
- ntile函数
# topN问题 sql模板
select *
from (
select *,
row_number() over (partition by 要分组的列名
order by 要排序的列名 desc) as 排名
from 表名) as a
where 排名 <= N;
datediff(日期1, 日期2):得到的结果是日期1与日期2相差的天数。
如果日期1比日期2大,结果为正;如果日期1比日期2小,结果为负
timestampdiff(时间类型, 日期1, 日期2)
这个函数和上面diffdate的正、负号规则刚好相反。
日期1大于日期2,结果为负,日期1小于日期2,结果为正。
使用union子查询代替or or会使索引失效
group_concat()函数将group by产生的同一个分组中的值连接起来,返回一个字符串结果。
case
UPDATE salary
SET
sex =
CASE sex
WHEN 'm' THEN 'f'
ELSE 'm'
END
1.查询第几高
176. 第二高的薪水
方法一:使用子查询和 LIMIT
子句
--子查询
select max(Salary) SecondHighestSalary
from Employee
where Salary <(select max(Salary) from Employee);
--或者`LIMIT`
select (
select DISTINCT Salary
from Employee
order by Salary DESC
limit 1,1)
as SecondHighestSalary;
方法二:使用 IFNULL
和 LIMIT
子句
select
ifNull(
(select distinct salary
from Employee order by Salary Desc
limit 1,1),null
) as SecondHighestSalary;
方法三:窗口函数
/* Write your T-SQL query statement below */
SELECT
MAX(s.Salary) AS SecondHighestSalary
FROM (
SELECT
e.Salary,
DENSE_RANK() OVER (ORDER BY e.Salary DESC) AS R
FROM Employee e
) s
WHERE R = 2
2.第n高
177. 第N高的薪水
CREATE FUNCTION getNthHighestSalary ( N INT ) RETURNS INT BEGIN
DECLARE m INT;
SET m = N - 1;
RETURN ( # Write your MySQL query statement below.
SELECT ifnull( ( SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT m, 1 ), NULL ) );
END
3.分数排名
leetCode解题系列 ⭐️ 178. 分数排名(Oracle dense_rank函数题解)
select
id
,score
,rank() over(order by score desc) rank --按照成绩排名,纯排名
,dense_rank() over(order by score desc) dense_rank --按照成绩排名,相同成绩排名一致
,row_number() over(order by score desc) row_number --按照成绩依次排名
,ntile(3) over (order by score desc) ntile --按照分数划分成绩梯队
from scores;
4.180. 连续出现的数字
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
5.185. 部门工资前三高的所有员工
https://leetcode-cn.com/problems/department-top-three-salaries/
SELECT
d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
Employee e1
JOIN
Department d ON e1.DepartmentId = d.Id
WHERE
3 > (SELECT
COUNT(DISTINCT e2.Salary)
FROM
Employee e2
WHERE
e2.Salary > e1.Salary
AND e1.DepartmentId = e2.DepartmentId
)
;
# topN问题 sql模板
select *
from (
select *,
row_number() over (partition by 要分组的列名
order by 要排序的列名 desc) as 排名
from 表名) as a
where 排名 <= N;
6.[197. 上升的温度]
https://leetcode-cn.com/problems/rising-temperature/
--# Write your MySQL query statement below
select a.id
from Weather a,Weather b
where datediff(a.recordDate,b.recordDate )=1 and a.Temperature>b.Temperature
7.595. 大的国家]
https://leetcode-cn.com/problems/big-countries/
SELECT name, population, area
FROM World
WHERE area > 3000000
UNION
SELECT name, population, area
FROM World
WHERE population > 25000000;
或者
select name,population,area
from World
where area >3000000 or population>25000000
8.596. 超过5名学生的课
https://leetcode-cn.com/problems/classes-more-than-5-students/)
SELECT CLASS
FROM COURSES
GROUP BY CLASS
HAVING COUNT(DISTINCT STUDENT) >= 5;
9.601. 体育馆的人流量
https://leetcode-cn.com/problems/human-traffic-of-stadium/
SELECT distinct a.*
FROM stadium as a,stadium as b,stadium as c
where ((a.id = b.id-1 and b.id+1 = c.id) or
(a.id-1 = b.id and a.id+1 = c.id) or
(a.id-1 = c.id and c.id-1 = b.id))
and (a.people>=100 and b.people>=100 and c.people>=100)
order by a.id;
10.[626. 换座位]
https://leetcode-cn.com/problems/exchange-seats/
# Write your MySQL query statement below
# SELECT
# (CASE
# WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
# WHEN MOD(id, 2) != 0 AND counts = id THEN id
# ELSE id - 1
# END) AS id,
# student
# FROM
# seat,
# (SELECT
# COUNT(*) AS counts
# FROM
# seat) AS seat_counts
# ORDER BY id ASC;
或者
select
if(id%2=0,
id-1,
if(id=(select count(distinct id) from seat),
id,
id+1))
as id,student
from seat
order by id;
11.[627. 变更性别]
https://leetcode-cn.com/problems/swap-salary/
一.case
UPDATE salary
SET
sex =
CASE sex
WHEN 'm' THEN 'f'
ELSE 'm'
END
二.if
UPDATE salary
SET
sex = IF(sex = 'f', 'm', 'f')
三.ASCII
UPDATE salary
SET sex = char(ascii('f') + ascii('m') - ascii(sex))
12.1179. 重新格式化部门表
https://leetcode-cn.com/problems/reformat-department-table/
select id,
sum(if(month='Jan',revenue,null)) as Jan_Revenue,
sum(if(month='Feb',revenue,null)) as Feb_Revenue,
sum(if(month='Mar',revenue,null)) as Mar_Revenue,
sum(if(month='Apr',revenue,null)) as Apr_Revenue,
sum(if(month='May',revenue,null)) as May_Revenue,
sum(if(month='Jun',revenue,null)) as Jun_Revenue,
sum(if(month='Jul',revenue,null)) as Jul_Revenue,
sum(if(month='Aug',revenue,null)) as Aug_Revenue,
sum(if(month='Sep',revenue,null)) as Sep_Revenue,
sum(if(month='Oct',revenue,null)) as Oct_Revenue,
sum(if(month='Nov',revenue,null)) as Nov_Revenue,
sum(if(month='Dec',revenue,null)) as Dec_Revenue
from department
group by id
或者
SELECT id,
SUM(CASE WHEN month='Jan' THEN revenue END) AS Jan_Revenue,
SUM(CASE WHEN month='Feb' THEN revenue END) AS Feb_Revenue,
SUM(CASE WHEN month='Mar' THEN revenue END) AS Mar_Revenue,
SUM(CASE WHEN month='Apr' THEN revenue END) AS Apr_Revenue,
SUM(CASE WHEN month='May' THEN revenue END) AS May_Revenue,
SUM(CASE WHEN month='Jun' THEN revenue END) AS Jun_Revenue,
SUM(CASE WHEN month='Jul' THEN revenue END) AS Jul_Revenue,
SUM(CASE WHEN month='Aug' THEN revenue END) AS Aug_Revenue,
SUM(CASE WHEN month='Sep' THEN revenue END) AS Sep_Revenue,
SUM(CASE WHEN month='Oct' THEN revenue END) AS Oct_Revenue,
SUM(CASE WHEN month='Nov' THEN revenue END) AS Nov_Revenue,
SUM(CASE WHEN month='Dec' THEN revenue END) AS Dec_Revenue
FROM department
GROUP BY id
ORDER BY id;