Sql算法总结

Sql例题

1. 获取第二高的薪水。（limit + offset）

SELECT 
IFNULL((SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
       LIMIT 1 OFFSET 1), NULL) AS SecondHighestSalary


1. select* from article LIMIT 1,3   # 从起始位置+1开始，取三条记录
2.select * from article LIMIT 3 OFFSET 1   # 从起始位置+1开始，取三条
上面两种写法都表示取2,3,4三条条数据

2. 获取第n高的薪水。(函数 + limit)

// 设计函数
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) as SecondHighestSalary
      # distinct 用于返回不同的值，相同的值会被去掉
      # 相同的工资会被去重，从精简后的工资序列中找出第n高的工资
      # limit后不可带表达式
  );
end

3. 编写一个 SQL 查询来实现分数排名。 (dense_rank over)

select Score, dense_rank() over (order by Score desc) as 'Rank' from Scores
// 依据dense_rank()的排序顺序输出Score的结果

rank()：相同分数有重复排名，但是重复后下一个人按照实际排名    1 1 3 4
row_number()：依次递增排名，无重复排名                      1 2 3 4
dense_rank()：分数一致排名一致，分数不一致排名+1             1 1 2 3
ntile(4)：分组排名，里面的数字是几，最多排名就是几，里面的数字是4，最多的排名就是4

这些排序函数相当于是在选出来的结果上再进行排序。

4. 查找所有至少连续出现三次的数字。（inner join）

select distinct a.num as "ConsecutiveNums" from logs as a 
inner join logs as b on a.id + 1 = b.id
inner join logs as c on b.id + 1 = c.id 
where a.num = b.num and b.num = c.num
# 先执行from中的内联表生成，会生成一张6列的表，分别是a.id a.num b.id b.num c.id c.num,然后再从表中搜寻符合where条件的记录，一边搜寻一边由select函数记录搜寻的结果，并对其结果进行去重

表连接主要分为三种，左连接（left join）, 右连接（right join）, 内连接（inner join）。
其中内连接是在右连接的基础上，去掉右表中左表中没有的字段记录。

5.  部门工资前三高的所有员工。 （ * ）

select de.name as Department, em.Name as Employee, em.Salary as Salary 
from Employee as em
inner join 

(select em.name as t2name, em.Salary as t2salary 
  from Employee as em 
  where 3 >             # 比当前员工的工资高的人，0，1，2
  ( select count(distinct e2.salary) 
    from Employee as e2 
    where e2.salary > em.salary and e2.DepartmentId = em.DepartmentId
  )                     # 查询子表返回的是count出来的数
) as r1                 # 求出数据表中每一个部门的前三高的工资

on em.name = r1.t2name 
inner join 
Department as de 
on de.id = em.departmentid
order by de.id, em.salary desc;  // 依据两个字段排序


1. 求出一个部门中前三高的工资和其对应的人，并将结果内联到Employee表

6. 删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。 （delete）

DELETE p1 FROM Person p1,   # 删除p1中的数据
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id   # 只留下id最小的

# 最后删除的还是person表，不是p1与p2组成的笛卡尔表

7. 262. 行程和用户.查出 "2013-10-01" 至 "2013-10-03" 期间非禁止用户（乘客和司机都必须未被禁止）的取消率。 ( with + case when + sum + round)

with A as(
    select t1.* from trips as t1  # 保留trips表中的所有记录即可
    inner join users as u1
    on t1.Client_Id = u1.Users_Id
    inner join users as u2
    on t1.Driver_Id = u2.Users_Id
    where (Request_at between '2013-10-01' and '2013-10-03')
    and u1.Banned = 'No' 
    and u2.Banned = 'No'
)

select Request_at as 'Day', 
round(sum(case A.status when "cancelled_by_driver" then 1  
                        when "cancelled_by_client" then 1
                        else 0 end)/count(*), 2) as 'Cancellation Rate' 
from A group by Request_at; # 
# IF(T.STATUS = 'completed',0,1)
# 1. 做一张子表，找出指定时段内的乘客和司机都未被禁止的记录。
# 2. 依据日期做聚类，求出每一天的取消率

8. 请列出所有超过或等于5名学生的课。(having count(distinct))

select class from courses group by class having count(distinct student) >= 5;
# having后多采用聚合函数 ***
# count(*)  group by 联合使用，得到的结果是每一个聚合之后的组的数据数量，而不是一共有多少个组。

# 如果想查询一共有多少个组，可以使用count(distinct 分组字段)即可 XXX，这样做的结果是得出每组分组字段下不同同值得记录数，答案肯定是1。正确得做法是，将第一次分组后得结果做子表，只为去重，第二次再做一次查询。

9.  编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。返回按 visit_date 升序排列的结果表。 ( * )

// 题目 https://leetcode-cn.com/problems/human-traffic-of-stadium/

select distinct t1.*
from stadium t1, stadium t2, stadium t3   # 做笛卡尔积
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
	(t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1)  -- t1, t2, t3
    or
    (t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
    or
    (t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
)
order by t1.id;

// 1. 由于至少要三个连续的数，所以先对三张表做笛卡尔积
// 2. 再从笛卡尔积表中找出所以的行记录，要求每一行的三个people均>100
// 3. 求连续三天的序列，t1 t2 t3 与t1 t3 t2 的结果一致，为了避免重复，只保留t1 t2 t3
// id：4 5 6 和id：4 6 5虽然在不同的行，但都是表示id=4那天是连续三天的第一天
// 注意数据会重复，4 5 6 7， 而以5为例，可能得序列是456，也可能567，所以数据要加distinct




with t1 as(
    select *, id - row_number() over(order by id) as rk  // 将最后得结果行数据依据id进行排名，并且将每一行得排名结果作为一列值
    // 在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、  order by 的执行。
    from stadium
    where people >= 100
)

select id,visit_date,people
from t1
where rk in(  // 依据rk进行聚合，得到一个序列，其中得值都是聚集数>=3得
    select rk
    from t1
    group by rk
    having count(rk) >= 3
)
// 这道题需要提前用With临时空间，是因为where子句中需要再次调用from中选取的表
// 看链接中得图可以就能明白
// 链接：https://leetcode-cn.com/problems/human-traffic-of-stadium/solution/tu-jie-lian-xu-ri-qi-ji-nan-dian-fen-xi-xnj58/

10. case when的使用。

# 1. 
with newseat as(
    select s2.id as id, s1.student as stu1 from seat as s1
    inner join seat as s2 
    on s1.id = case   # case
    when s1.id = (select count(*) from seat) and s1.id % 2 = 1 then s2.id
    when s1.id % 2 = 0 then s2.id+1
    else s2.id-1
    end
)  # 在右连接的基础上，将左表中没有匹配的记录从右表中删除 ***

select id, stu1 as student from newseat;


# case when then else end 表达式必须返回一个值，该值就是then后的值
# (select count(*) from seat) 可以用来统计数据用于数值比较


# 2. 性别变更
update salary set sex = 
case sex
when 'm' then 'f'
else 'm'
end;


# 3. 重新格式化部门表
select id,
    sum(case month when 'Jan' then revenue end) as Jan_Revenue,  // 将符合条件的所有的revenue相加
    sum(case month when 'Feb' then revenue end) as Feb_Revenue,
    sum(case month when 'Mar' then revenue end) as Mar_Revenue,
    sum(case month when 'Apr' then revenue end) as Apr_Revenue,
    sum(case month when 'May' then revenue end) as May_Revenue,
    sum(case month when 'Jun' then revenue end) as Jun_Revenue,
    sum(case month when 'Jul' then revenue end) as Jul_Revenue,
    sum(case month when 'Aug' then revenue end) as Aug_Revenue,
    sum(case month when 'Sep' then revenue end) as Sep_Revenue,
    sum(case month when 'Oct' then revenue end) as Oct_Revenue,
    sum(case month when 'Nov' then revenue end) as Nov_Revenue,
    sum(case month when 'Dec' then revenue end) as Dec_Revenue
from Department
group by id 



# 4. 对工资分类
SELECT 
CASE # 对得到的结果做处理，所以就没有变量
WHEN salary <= 500 THEN '1' 
WHEN salary > 500 AND salary <= 600  THEN '2' 
WHEN salary > 600 AND salary <= 800  THEN '3' 
WHEN salary > 800 AND salary <= 1000 THEN '4' 
ELSE NULL 
END 
as salary_class,  # 将case的结果定义一个新的变量
COUNT(*) 
FROM    Table_A 
GROUP BY 
CASE WHEN salary <= 500 THEN '1' 
WHEN salary > 500 AND salary <= 600  THEN '2' 
WHEN salary > 600 AND salary <= 800  THEN '3' 
WHEN salary > 800 AND salary <= 1000 THEN '4' 
ELSE NULL END;

11. 窗口函数。

1. 在当第n个员工行处，输出前N个当前员工的salary累计和。  （NC SQL60）
SELECT emp_no,salary,SUM(salary) OVER(ORDER BY emp_no) AS running_total
FROM salaries
WHERE to_date = '9999-01-01';

/*
窗口的使用，会对经过where和having处理后的数据在进行处理一遍。窗口函数会对从第1条开始直到当前第n条数据所形成的数据集进行处理，无论排名也好，聚合函数也好。
*/

2. 窗口函数与非窗口函数中的sum函数的区别
select *,sum(bookid), sum(bookid) over(order by bookid) as "rank" from test group by ntl;
第一个sum求的是以group by分组后的每一个小组中的bookid的总和。
第二个sum求得是窗口函数中从第一条到现在的条数据中bookid的总和，这里的数据相当于是由group by分组后的每组的第一条数据的汇总。

数据处理三部分

1. where用于在从磁盘读取数据的时候进行过滤。

2. 在SQL 中增加having子句原因是,where关键字无法与合计函数一起使用，having是对内存中的数据进行处理。

3. 最后是窗口，窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。窗口函数存在的作用主要是搭配专用窗口函数row_number等，当然，也可以结合聚合函数一起是用。

注意点：

1. 某个字段是否为空：where name is null

2. 求两个日期之间的差值：where datediff(w2.RecordDate, w1.RecordDate) = 1 

3. 求小数点后两位：round(sum(...), 2)

4. 不等于 <>

5. 结果为空返回null：ifnull((select distinct salary from Employee order by salary desc limit m, 1), null)

6. 窗口函数介绍

1. 窗口的作用的就是对经过where和having处理后的数据惊醒一次再处理，然后按照处理后的结果顺序输出。
2. ROW_NUMBER() over(partition by [姓名] order by [打卡时间] desc) as "rowNum"    
   // 让数据按照姓名分组，并且在每组内部按照时间进行排序。partition by可以完成二次聚类。

7. 窗口函数与聚合函数一起使用的情况

select *,sum(bookid), row_number() over(order by bookid) as "rank"
from test group by ntl; 
使用group by后，每个组只能保留一条数据记录。
sum统计的是该组中的所有的bookid数据的和，而row_number则是对每组中的第一条数据进行排序。

Sql算法题中主要可以分为：

1. 对表进行处理，表与表之间进行连接，以及生成子表。主要分为4种，左连接（left join）, 右连接（right join）, 内连接（inner join）和笛卡尔表（... from student as s1, student as s2 ...）。

2. 五子句的结合使用，where, group by, having, order by, limit -- 五子句的先后顺序。现有group by，再有order by，order by是对group by的结果进行排序，select 从group by中获取结果的时候，得到的每一组的第一条记录。 

3. group by以及一些聚合函数的使用，例如sum，max，count等，having需要结合group by来使用。举例，统计学生成绩表中每个学生所有科目的总成绩大于500的学生信息，group by student having count(grade) > 500。注意，聚合函数不可以直接嵌套使用，比如： max(count(*))！！！

4. sql的一些表达式。case name when "A1" then 1 else 2 end，需要返回一个具体的结果。 

5. 生成子表。 with newtable as ( select 语句 )

https://blog.51cto.com/u_16213304/8619036 msyql中为了提升sql执行效率不允许使用with