MYSQL高阶技能之函数和自定义函数

一.CASE函数

变量

set @test = 1;
select count(*) from school.student into @test;
select @test;
replace into s_count (c) values (@test);

sql中用一个@符号开头表示变量。

CASE函数：CASE…………END

-----比如：计算及格不及格------
    select (case 
    when score>=90 then '优秀'
    when score>=80 then '良好'
    when score>=60 then '及格' 
    else '不及格'
    end) as score1,s_id,score from total;

你可以思考一下：用case计算学生，根据他们的出生年月，分为80前 80后 90后，00后

SELECT *,(CASE WHEN year < 1980 THEN '80前' 
       WHEN year >= 1980 AND year <1990 THEN '80后'
       WHEN year >= 1990 AND year <2000 THEN '90后'
       ELSE '00后'
  END) AS a
FROM
 (SELECT *,SUBSTR(S_age,1,4) AS year
 FROM Student) t1

查询结果：

 CASE函数其实有两种写法

--简单Case函数
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
---这两种方式，可以实现相同的功能。

二.窗口函数

----在组内进行排序---
SELECT *,ROW_NUMBER() OVER(PARTITION BY C_id ORDER BY Score DESC) AS ranking
 FROM Total;
---括号内无条件的话，输出序号--
SELECT *,ROW_NUMBER() OVER() AS r
 from total;

1.最典型的例子：排名，序号

----运行三个窗口函数，比较结果
SELECT Score,RANK() OVER(ORDER BY Score DESC) AS a,
   ROW_NUMBER() OVER (ORDER BY Score DESC) AS b,
   dense_rank() OVER (ORDER BY Score DESC) AS c
   FROM Total;

Score	a	b	c
99	1	1	1
98	2	2	2
90	3	3	3
89	4	4	4
87	5	5	5
80	6	6	6
80	6	7	6
80	6	8	6
80	6	9	6
80	6	10	6
76	11	11	7
70	12	12	8
60	13	13	9
50	14	14	10
34	15	15	11
31	16	16	12
30	17	17	13
20	18	18	14
NULL	19	19	15

RANK()函数：排名，如上述结果同为80分的都是第六名，76分的为第11名，与我们日常比赛的排                         名方式一致；（并列跳过）

ROW_NUMBER()函数： 同为80分的五个同学分别为第6、7、8、9、10名；

DENSE_RANK()函数：同为80分的都是第六名，76分的为第七名（并列不跳过）。

2.计算第几个（也就是把本组的第几个补充到当前 ）

select first_value(Score) over (PARTITION by c_id ORDER by score DESC) as r,s_id,c_id,score
from total;
-----按C_id分组，然后将每一组内的第一个Score补充到当前，也即是输出每一组（每个课程）的最高分

 

3.查询前两行到当行的平均值（移动平均）

select AVG(score) over (PARTITION  by c_id ORDER BY score desc ROWS 2 preceding) as r,s_id,c_id,score
from total;

常用的窗口函数：(20条消息) 常用的窗口函数_简之的博客-优快云博客

三、自定义函数

--将自定义函数的功能开启
SHOW VARIABLES LIKE '%func%';
set global log_bin_trust_function_creators = 'ON' ;
--自定义一个f_add函数。begin和end中间可以修改，为函数主体部分
delimiter $$
drop function if exists `f_add` $$
create function `f_add`(a int , b int) returns int
begin
return a + b;
end$$
delimiter ;
--验证
SELECT f_add(1,2);