sql語(yǔ)句的執(zhí)行順序
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
一。通解任意連續(xù)N問(wèn)題,leetcode180：構(gòu)造輔助列
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;
二。通解求連續(xù)值問(wèn)題,leetcode1285：用rank或row_number找到連續(xù)區(qū)間開(kāi)始和結(jié)束的數(shù)? ? ? ? ? ?字
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;
三，移動(dòng)求和，移動(dòng)平均
https://michael.blog.csdn.net/article/details/107729626?leetcode579
sum(Salary) over(partition by Id order by Month rows 2 preceding)
rows 數(shù)字 preceding 取之前的行
rows 數(shù)字 following? 取之后的行
rows between 數(shù)字 preceding and 數(shù)字 following 取之前n行和之后n行
截至之前的“數(shù)字”行，也就是最靠近的“數(shù)字+1”行，包括當(dāng)前行
除去最近一個(gè)月，剩下每個(gè)月的近3個(gè)月，這時(shí)應(yīng)該 （2 preceding）
四，行列轉(zhuǎn)換。http://www.itdecent.cn/p/1c6fb0df9f58
? ? ? ?行轉(zhuǎn)列：用 sum case when，group by。科目字段中的語(yǔ)文，數(shù)學(xué)，英語(yǔ)就被單獨(dú)拎出? ? ? ? ? ? ? ? ? ? ? ?來(lái)成了三個(gè)列，三個(gè)字段，稱(chēng)為行轉(zhuǎn)列。也可以用pivot函數(shù)。
? ? ? ? ? ? ? ? ? ? ? select * from student? ?pivot(sum(score) for subject in (語(yǔ)文，數(shù)學(xué)，英語(yǔ))。? ? ? ? ? ? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? ? ? ? pivot后面跟一個(gè)聚合函數(shù)來(lái)拿結(jié)果，for后面跟的科目是要轉(zhuǎn)的列，in后面就是? ? ? ? ? ? ? ? ? ? ? ? 具體科目值。
? ? ? ? ? ? ? ? ? ? ? ?select name,sum(case when subject = ''語(yǔ)文" then score else 0 end) "語(yǔ)文"
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?sum(case when subject = "數(shù)學(xué)" then score else 0 end) "數(shù)學(xué)"
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?sum(case when subject = "英語(yǔ)" then score else 0 end) "英語(yǔ)"
? ? ? ? ? ? ? ? ? ? ? ?from student
? ? ? ? ? ? ? ? ? ? ? ?group by name
? ? ? ?列轉(zhuǎn)行：用union all。union all 和union 構(gòu)造新字段，連接行。union去重。unpivot
? ? ? ? ? ? ? ? ? ? ? select * from student unpivot(score for subject in (語(yǔ)文，數(shù)學(xué)，英語(yǔ)）
? ? ? ? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? ? ? ?select name,"語(yǔ)文"as subject ,sum(語(yǔ)文) score from student group by name
? ? ? ? ? ? ? ? ? ? ?union all
? ? ? ? ? ? ? ? ? ? ?select name,"數(shù)學(xué)" as subject,sum(數(shù)學(xué)) score from student group by name
? ? ? ? ? ? ? ? ? ? ?union all
? ? ? ? ? ? ? ? ? ? ?select name,"英語(yǔ)" as subject,sum(英語(yǔ)) score from student group by name
? ? ? ? ? ? ? ? ? ? ? 是要把語(yǔ)文，數(shù)學(xué)，英語(yǔ)這三個(gè)列，三個(gè)字段合并成一個(gè)字段，一個(gè)列，一個(gè)? ? ? ? ? ? ? ? ? ? ? ? 字段下的內(nèi)容，所以稱(chēng)為列轉(zhuǎn)行。
五，all select 求世界上人口最多的國(guó)家
? ? ? ? select name from world where population >= all (select population from world where? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? population >0)
六，窗口函數(shù)
? ? ?分布函數(shù) : cume_list? ?https://www.begtut.com/mysql/mysql-cume_dist-function.html??
? ? ? ? ? ? ? ? ? ? ? percent_rank?https://www.begtut.com/mysql/mysql-percent_rank-function.html
? ? ?頭尾函數(shù)：first_value?https://www.begtut.com/mysql/mysql-first_value-function.html
