sql语句的执行顺序
1.from
2.on
3.join
4.where
5.group by
6.having
7.select
8.distinct
9.order by
10.limit
7。select 8。 distinct
1。from
3。join
2。on
4。where
5。group by
6。having
9。order by
10。limit
一。通解任意连续N问题,leetcode180：构造辅助列
select distinct Num as ConsecutiveNums
from (select * ,row_number()over(partition by Num order by Id) as rownum,
row_number()over(order by Id) as id2
from logs) as t
group by (id2-rownum),Num
having count(*) >= n;
二。通解求连续值问题,leetcode1285：用rank或row_number找到连续区间开始和结束的数 字
select min(log_id) as start_id,max(log_id) as end_id
from (select distinct log_id, row_number()over(order by log_id) as rn,
log_id - row_number()over(order by log_id) as reference
from Logs ) as t
group by reference
order by start_id;
三，移动求和，移动平均
https://michael.blog.csdn.net/article/details/107729626 leetcode579
sum(Salary) over(partition by Id order by Month rows 2 preceding)
rows 数字 preceding 取之前的行
rows 数字 following 取之后的行
rows between 数字 preceding and 数字 following 取之前n行和之后n行
截至之前的“数字”行，也就是最靠近的“数字+1”行，包括当前行
除去最近一个月，剩下每个月的近3个月，这时应该 （2 preceding）
四，行列转换。https://www.jianshu.com/p/1c6fb0df9f58
行转列：用 sum case when，group by。科目字段中的语文，数学，英语就被单独拎出 来成了三个列，三个字段，称为行转列。也可以用pivot函数。
select * from student pivot(sum(score) for subject in (语文，数学，英语)。
pivot后面跟一个聚合函数来拿结果，for后面跟的科目是要转的列，in后面就是 具体科目值。
select name,sum(case when subject = ''语文" then score else 0 end) "语文"
sum(case when subject = "数学" then score else 0 end) "数学"
sum(case when subject = "英语" then score else 0 end) "英语"
from student
group by name
列转行：用union all。union all 和union 构造新字段，连接行。union去重。unpivot
select * from student unpivot(score for subject in (语文，数学，英语）
select name,"语文"as subject ,sum(语文) score from student group by name
union all
select name,"数学" as subject,sum(数学) score from student group by name
union all
select name,"英语" as subject,sum(英语) score from student group by name
是要把语文，数学，英语这三个列，三个字段合并成一个字段，一个列，一个 字段下的内容，所以称为列转行。
五，all select 求世界上人口最多的国家
select name from world where population >= all (select population from world where population >0)
六，窗口函数
分布函数 : cume_list https://www.begtut.com/mysql/mysql-cume_dist-function.html
percent_rank https://www.begtut.com/mysql/mysql-percent_rank-function.html
头尾函数：first_value https://www.begtut.com/mysql/mysql-first_value-function.html
