sql小小总结

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. 第二高的薪水

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;

13.0901SQL53 按照dept_no进行汇总