1. 控制流函數(shù)
| 格式 |
解釋 |
案例 |
| IF(expr,v1,v2) |
如果表達(dá)式 expr 成立，返回結(jié)果 v1；否則，返回結(jié)果 v2。 |
SELECT IF(1 > 0,'正確','錯誤')??? ->正確 |
| IFNULL(v1,v2） |
如果 v1 的值不為 NULL，則返回 v1，否則返回 v2。 |
SELECT IFNULL(null,'Hello Word') ->Hello Word |
| ISNULL(expression) |
判斷表達(dá)式是否為 NULL |
SELECT ISNULL(NULL); ->1 |
| NULLIF(expr1, expr2) |
比較兩個字符串，如果字符串 expr1 與 expr2 相等 返回 NULL，否則返回 expr1 |
SELECT NULLIF(25, 25); -> |
| 格式 |
解釋 |
操作 |
| CASE expression ??? WHEN condition1 THEN result1 ??? WHEN condition2 THEN result2 ?? ... ??? WHEN conditionN THEN resultN ??? ELSE result END |
CASE 表示函數(shù)開始，END 表示函數(shù)結(jié)束。如果 condition1 成立，則返回 result1, 如果 condition2 成立，則返回 result2，當(dāng)全部不成立則返回 result，而當(dāng)有一個成立之后，后面的就不執(zhí)行了。 |
select case 100 when 50 then 'tom' when 100 then 'mary'else 'tim' end ; select case when 1=2 then 'tom' when 2=2 then 'mary' else'tim' end ; |
use mydb4;
-- 創(chuàng)建訂單表
create table orders(
oid int primary key, -- 訂單id
price double, -- 訂單價格
payType int -- 支付類型(1:微信支付 2:支付寶支付 3:銀行卡支付 4：其他)
);
?
insert into orders values(1,1200,1);
insert into orders values(2,1000,2);
insert into orders values(3,200,3);
insert into orders values(4,3000,1);
insert into orders values(5,1500,2);
-- 方式1
select
* ,
case
when payType=1 then '微信支付'
when payType=2 then '支付寶支付'
when payType=3 then '銀行卡支付'
else '其他支付方式'
end as payTypeStr
from orders;
-- 方式2
select
* ,
case payType
when 1 then '微信支付'
when 2 then '支付寶支付'
when 3 then '銀行卡支付'
else '其他支付方式'
end as payTypeStr
from orders;
2. 窗口函數(shù)
非聚合窗口函數(shù)是相對于聚合函數(shù)來說的。聚合函數(shù)是對一組數(shù)據(jù)計(jì)算后返回單個值（即分組），非聚合函數(shù)一次只會處理一行數(shù)據(jù)。窗口聚合函數(shù)在行記錄上計(jì)算某個字段的結(jié)果時，可將窗口范圍內(nèi)的數(shù)據(jù)輸入到聚合函數(shù)中，并不改變行數(shù)。
-- 語法：
window_function ( expr ) OVER (
PARTITION BY ...
ORDER BY ...
frame_clause
)
其中，window_function 是窗口函數(shù)的名稱；expr 是參數(shù)，有些函數(shù)不需要參數(shù)；OVER子句包
含三個選項(xiàng)：
分區(qū)（PARTITION BY）：
PARTITION BY選項(xiàng)用于將數(shù)據(jù)行拆分成多個分區(qū)（組），它的作用類似于GROUP BY分組。如
果省略了 PARTITION BY，所有的數(shù)據(jù)作為一個組進(jìn)行計(jì)算
排序（ORDER BY）：
OVER 子句中的ORDER BY選項(xiàng)用于指定分區(qū)內(nèi)的排序方式，與 ORDER BY 子句的作用類似
以及窗口大小（frame_clause）：
frame_clause選項(xiàng)用于在當(dāng)前分區(qū)內(nèi)指定一個計(jì)算窗口，也就是一個與當(dāng)前行相關(guān)的數(shù)據(jù)子集。
數(shù)據(jù)準(zhǔn)備：
use mydb4;
create table employee(
dname varchar(20), -- 部門名
eid varchar(20),
ename varchar(20),
hiredate date, -- 入職日期
salary double -- 薪資
);
insert into employee values('研發(fā)部','1001','劉備','2021-11-01',3000);
insert into employee values('研發(fā)部','1002','關(guān)羽','2021-11-02',5000);
insert into employee values('研發(fā)部','1003','張飛','2021-11-03',7000);
insert into employee values('研發(fā)部','1004','趙云','2021-11-04',7000);
insert into employee values('研發(fā)部','1005','馬超','2021-11-05',4000);
insert into employee values('研發(fā)部','1006','黃忠','2021-11-06',4000);
?
insert into employee values('銷售部','1007','曹操','2021-11-01',2000);
insert into employee values('銷售部','1008','許褚','2021-11-02',3000);
insert into employee values('銷售部','1009','典韋','2021-11-03',5000);
insert into employee values('銷售部','1010','張遼','2021-11-04',6000);
insert into employee values('銷售部','1011','徐晃','2021-11-05',9000);
insert into employee values('銷售部','1012','曹洪','2021-11-06',6000);
?2.1 序號函數(shù)
序號函數(shù)有三個：ROW_NUMBER()、RANK()、DENSE_RANK()，可以用來實(shí)現(xiàn)分組排序，并添
加序號。
-- 語法：
row_number()|rank()|dense_rank() over (
partition by ...
order by ...
)
-- 對每個部門的員工按照薪資排序，并給出排名
select
dname,
ename,
salary,
row_number() over(partition by dname order by salary desc) as rn
from employee;
-- 對每個部門的員工按照薪資排序，并給出排名 rank
select
dname,
ename,
salary,
rank() over(partition by dname order by salary desc) as rn
from employee;
-- 對每個部門的員工按照薪資排序，并給出排名 dense-rank
select
dname,
ename,
salary,
dense_rank() over(partition by dname order by salary desc) as rn
from employee;
--求出每個部門薪資排在前三名的員工- 分組求TOPN
select
*
from
(
select
dname,
ename,
salary,
dense_rank() over(partition by dname order by salary desc) as rn
from employee
)t
where t.rn <= 3
-- 對所有員工進(jìn)行全局排序（不分組）
-- 不加partition by表示全局排序
select
dname,
ename,
salary,
dense_rank() over( order by salary desc) as rn
from employee;
2.2 開窗聚合函數(shù)
select
dname,
ename,
salary,
sum(salary) over(partition by dname order by hiredate) as pv1
from employee;
?
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid) as pv3
from itcast_t1; -- 如果沒有order by排序語句 默認(rèn)把分組內(nèi)的所有數(shù)據(jù)進(jìn)行sum操作
select
dname,
ename,
salary,
sum(salary) over(partition by dname order by hiredate rows between unbounded preceding and current row) as c1
from employee;
?
select
dname,
ename,
salary,
sum(salary) over(partition by dname order by hiredate rows between 3 preceding and current row) as c1
from employee;
select
dname,
ename,
salary,
sum(salary) over(partition by dname order by hiredate rows between 3 preceding and 1 following) as c1
from employee;
select
dname,
ename,
salary,
sum(salary) over(partition by dname order by hiredate rows between current row and unbounded following) as c1
from employee;
2.3 分布函數(shù)
CUME_DIST：
用途：分組內(nèi)小于、等于當(dāng)前rank值的行數(shù) / 分組內(nèi)總行數(shù)
應(yīng)用場景：查詢小于等于當(dāng)前薪資（salary）的比例
select
dname,
ename,
salary,
cume_dist() over(order by salary) as rn1, -- 沒有partition語句 所有的數(shù)據(jù)位于一組
cume_dist() over(partition by dept order by salary) as rn2
from employee;
/*
rn1: 沒有partition,所有數(shù)據(jù)均為1組，總行數(shù)為12，
第一行：小于等于3000的行數(shù)為3，因此，3/12=0.25
第二行：小于等于4000的行數(shù)為5，因此，5/12=0.4166666666666667
rn2: 按照部門分組，dname='研發(fā)部'的行數(shù)為6,
第一行：研發(fā)部小于等于3000的行數(shù)為1，因此，1/6=0.16666666666666666
*/
PERCENT_RANK?：
每行按照公式(rank-1) / (rows-1)進(jìn)行計(jì)算。其中，rank為RANK()函數(shù)產(chǎn)生的序號，rows為當(dāng)前窗
口的記錄總行數(shù)
select
dname,
ename,
salary,
rank() over(partition by dname order by salary desc ) as rn,
percent_rank() over(partition by dname order by salary desc ) as rn2
from employee;
/*
rn2:
第一行: (1 - 1) / (6 - 1) = 0
第二行: (1 - 1) / (6 - 1) = 0
第三行: (3 - 1) / (6 - 1) = 0.4
*/
2.4 前后函數(shù)
返回位于當(dāng)前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值
用途：查詢前1名同學(xué)的成績和當(dāng)前同學(xué)成績的差值
-- lag的用法
select
dname,
ename,
hiredate,
salary,
lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,
lag(hiredate,2) over(partition by dname order by hiredate) as last_2_time
from employee;
/*
last_1_time: 指定了往上第1行的值，default為'2000-01-01'
第一行，往上1行為null,因此取默認(rèn)值 '2000-01-01'
第二行，往上1行值為第一行值，2021-11-01
第三行，往上1行值為第二行值，2021-11-02
last_2_time: 指定了往上第2行的值，為指定默認(rèn)值
第一行，往上2行為null
第二行，往上2行為null
第四行，往上2行為第二行值，2021-11-01
第七行，往上2行為第五行值，2021-11-02
*/
-- lead的用法
select
dname,
ename,
hiredate,
salary,
lead(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,
lead(hiredate,2) over(partition by dname order by hiredate) as last_2_time
from employee;
2.5 頭尾函數(shù)
返回第一個（FIRST_VALUE(expr)）或最后一個（LAST_VALUE(expr)）expr的值
應(yīng)用場景：截止到當(dāng)前，按照日期排序查詢第1個入職和最后1個入職員工的薪資
-- 注意, 如果不指定ORDER BY，則進(jìn)行排序混亂，會出現(xiàn)錯誤的結(jié)果
select
dname,
ename,
hiredate,
salary,
first_value(salary) over(partition by dname order by hiredate) as first,
last_value(salary) over(partition by dname order by hiredate) as last
from employee;
2.6 其他函數(shù)
NTH_VALUE(expr,n)：
返回窗口中第n個expr的值。expr可以是表達(dá)式，也可以是列名
應(yīng)用場景：截止到當(dāng)前薪資，顯示每個員工的薪資中排名第2或者第3的薪資
-- 查詢每個部門截止目前薪資排在第二和第三的員工信息
select
dname,
ename,
hiredate,
salary,
nth_value(salary,2) over(partition by dname order by hiredate) as second_score,
nth_value(salary,3) over(partition by dname order by hiredate) as third_score
from employee
NTILE：?
將分區(qū)中的有序數(shù)據(jù)分為n個等級，記錄等級數(shù)
應(yīng)用場景：將每個部門員工按照入職日期分成3組
-- 根據(jù)入職日期將每個部門的員工分成3組
select
dname,
ename,
hiredate,
salary,
ntile(3) over(partition by dname order by hiredate ) as rn
from employee;
-- 取出每個部門的第一組員工
select
*
from
(
SELECT
dname,
ename,
hiredate,
salary,
NTILE(3) OVER(PARTITION BY dname ORDER BY hiredate ) AS rn
FROM employee
)t
where t.rn = 1;
?文章來源地址http://www.zghlxwxcb.cn/news/detail-443457.html
（日常美圖時間）
文章來源：http://www.zghlxwxcb.cn/news/detail-443457.html
?
到了這里，關(guān)于MySQL---控制流函數(shù)、窗口函數(shù)（序號函數(shù)、開窗聚合函數(shù)、分布函數(shù)、前后函數(shù)、頭尾函數(shù)、其他函數(shù)）的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
