sql按条件查询最大最小值
sql向上/向下取整的函数，是什么？
sql向上/向下取整的函数，是什么？
ceil 用 法： double ceil(double x) 功 能： 返回大于或者等于指定表达式的最小整数
floor：CEIL(n)函数：取小于等于数值n的最小整数；
数值类型与字符串类型是有区别的，数学运算可以用数值类型进行运算，然后转型为字符串类型。
如何使用MySQL中的实用函数及查询技巧？
一直以来，MySQL 只有针对聚合函数的汇总类功能，比如MAX, AVG 等，没有从 SQL 层针对聚合类每组展开处理的功能。不过 MySQL 开放了 UDF 接口，可以用 C 来自己写UDF，这个就增加了功能行难度。
这种针对每组展开处理的功能就叫窗口函数，有的数据库叫分析函数。
在 MySQL 8.0 之前，我们想要得到这样的结果，就得用以下几种方法来实现：
1. session 变量
2. group_concat 函数组合
3. 自己写 store routines
接下来我们用经典的 学生/课程/成绩 来做窗口函数演示
准备
学生表
mysqlgt show create table student G*************************** 1. row ***************************Table: studentCreate Table: CREATE TABLE student (sid int(10) unsigned NOT NULL,sname varchar(64) DEFAULT NULL,PRIMARY KEY (sid)) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci1 row in set (0.00 sec)课程表
mysqlgt show create table courseG*************************** 1. row ***************************Table: courseCreate Table: CREATE TABLE `course` (`cid` int(10) unsigned NOT NULL,`cname` varchar(64) DEFAULT NULL,PRIMARY KEY (`cid`)) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci1 row in set (0.00 sec)成绩表
mysqlgt show create table scoreG*************************** 1. row ***************************Table: scoreCreate Table: CREATE TABLE `score` (`sid` int(10) unsigned NOT NULL,`cid` int(10) unsigned NOT NULL,`score` tinyint(3) unsigned DEFAULT NULL,PRIMARY KEY (`sid`,`cid`)) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci1 row in set (0.00 sec)测试数据
mysqlgt select * from student ----------- -------------- | sid | sname | ----------- -------------- | 201910001 | 张三 || 201910002 | 李四 || 201910003 | 武松 || 201910004 | 潘金莲 || 201910005 | 菠菜 || 201910006 | 杨发财 || 201910007 | 欧阳修 || 201910008 | 郭靖 || 201910009 | 黄蓉 || 201910010 | 东方不败 | ----------- -------------- 10 rows in set (0.00 sec)
mysqlgt select * from score ----------- ---------- ------- | sid | cid | score | ----------- ---------- ------- | 201910001 | 20192001 | 50 || 201910001 | 20192002 | 88 || 201910001 | 20192003 | 54 || 201910001 | 20192004 | 43 || 201910001 | 20192005 | 89 || 201910002 | 20192001 | 79 || 201910002 | 20192002 | 97 || 201910002 | 20192003 | 82 || 201910002 | 20192004 | 85 || 201910002 | 20192005 | 80 || 201910003 | 20192001 | 48 || 201910003 | 20192002 | 98 || 201910003 | 20192003 | 47 || 201910003 | 20192004 | 41 || 201910003 | 20192005 | 34 || 201910004 | 20192001 | 81 || 201910004 | 20192002 | 69 || 201910004 | 20192003 | 67 || 201910004 | 20192004 | 99 || 201910004 | 20192005 | 61 || 201910005 | 20192001 | 40 || 201910005 | 20192002 | 52 || 201910005 | 20192003 | 39 || 201910005 | 20192004 | 74 || 201910005 | 20192005 | 86 || 201910006 | 20192001 | 42 || 201910006 | 20192002 | 52 || 201910006 | 20192003 | 36 || 201910006 | 20192004 | 58 || 201910006 | 20192005 | 84 || 201910007 | 20192001 | 79 || 201910007 | 20192002 | 43 || 201910007 | 20192003 | 79 || 201910007 | 20192004 | 98 || 201910007 | 20192005 | 88 || 201910008 | 20192001 | 45 || 201910008 | 20192002 | 65 || 201910008 | 20192003 | 90 || 201910008 | 20192004 | 89 || 201910008 | 20192005 | 74 || 201910009 | 20192001 | 73 || 201910009 | 20192002 | 42 || 201910009 | 20192003 | 95 || 201910009 | 20192004 | 46 || 201910009 | 20192005 | 45 || 201910010 | 20192001 | 58 || 201910010 | 20192002 | 52 || 201910010 | 20192003 | 55 || 201910010 | 20192004 | 87 || 201910010 | 20192005 | 36 | ----------- ---------- ------- 50 rows in set (0.00 sec)
mysqlgt select * from course ---------- ------------ | cid | cname | ---------- ------------ | 20192001 | mysql || 20192002 | oracle || 20192003 | postgresql || 20192004 | mongodb || 20192005 | dble | ---------- ------------ 5 rows in set (0.00 sec)MySQL 8.0 之前
比如我们求成绩排名前三的学生排名，我来举个用 session 变量和 group_concat 函数来分别实现的例子：
session 变量方式
每组开始赋一个初始值序号和初始分组字段。
,,, c.ranking_scoreFROMstudent a,course b,(SELECTc.*,IF(@cid ,@rn : @rn 1,@rn : 1) AS ranking_score,@cid : AS tmpcidFROM(SELECT*FROMscoreORDER BY cid,score DESC) c,( : 0 rn,@cid : ) initialize_table) cWHERE c.ranking_score lt 3ORDER BY ,c.ranking_score
------------ ----------- ------- --------------- | cname | sname | score | ranking_score | ------------ ----------- ------- --------------- | dble | 张三 | 89 | 1 || dble | 欧阳修 | 88 | 2 || dble | 菠菜 | 86 | 3 || mongodb | 潘金莲 | 99 | 1 || mongodb | 欧阳修 | 98 | 2 || mongodb | 郭靖 | 89 | 3 || mysql | 李四 | 100 | 1 || mysql | 潘金莲 | 81 | 2 || mysql | 欧阳修 | 79 | 3 || oracle | 武松 | 98 | 1 || oracle | 李四 | 97 | 2 || oracle | 张三 | 88 | 3 || postgresql | 黄蓉 | 95 | 1 || postgresql | 郭靖 | 90 | 2 || postgresql | 李四 | 82 | 3 | ------------ ----------- ------- --------------- 15 rows in set, 5 warnings (0.01 sec)group_concat 函数方式
利用 findinset 内置函数来返回下标作为序号使用。
SELECT*FROM(,,,FIND_IN_SET(, ) score_rankingFROMstudent a,course b,score c,(SELECTcid,GROUP_CONCAT(scoreORDER BY score DESC SEPARATOR ,) gpFROMscoreGROUP BY cidORDER BY score DESC) dWHERE BY ,score_ranking) yttWHERE score_ranking lt 3；
------------ ----------- ------- --------------- | cname | sname | score | score_ranking | ------------ ----------- ------- --------------- | dble | 张三 | 89 | 1 || dble | 欧阳修 | 88 | 2 || dble | 菠菜 | 86 | 3 || mongodb | 潘金莲 | 99 | 1 || mongodb | 欧阳修 | 98 | 2 || mongodb | 郭靖 | 89 | 3 || mysql | 李四 | 100 | 1 || mysql | 潘金莲 | 81 | 2 || mysql | 欧阳修 | 79 | 3 || oracle | 武松 | 98 | 1 || oracle | 李四 | 97 | 2 || oracle | 张三 | 88 | 3 || postgresql | 黄蓉 | 95 | 1 || postgresql | 郭靖 | 90 | 2 || postgresql | 李四 | 82 | 3 | ------------ ----------- ------- --------------- 15 rows in set (0.00 sec)MySQL 8.0 窗口函数
MySQL 8.0 后提供了原生的窗口函数支持，语法和大多数数据库一样，比如还是之前的例子：
用 row_number() over () 直接来检索排名。
mysqlgtSELECT*FROM(,,,row_number() over (PARTITION BY BY DESC) score_rankFROMstudent AS a,course AS b,score AS cWHERE ) yttWHERE score_rank lt 3
------------ ----------- ------- ------------ | cname | sname | score | score_rank | ------------ ----------- ------- ------------ | dble | 张三 | 89 | 1 || dble | 欧阳修 | 88 | 2 || dble | 菠菜 | 86 | 3 || mongodb | 潘金莲 | 99 | 1 || mongodb | 欧阳修 | 98 | 2 || mongodb | 郭靖 | 89 | 3 || mysql | 李四 | 100 | 1 || mysql | 潘金莲 | 81 | 2 || mysql | 欧阳修 | 79 | 3 || oracle | 武松 | 98 | 1 || oracle | 李四 | 97 | 2 || oracle | 张三 | 88 | 3 || postgresql | 黄蓉 | 95 | 1 || postgresql | 郭靖 | 90 | 2 || postgresql | 李四 | 82 | 3 | ------------ ----------- ------- ------------ 15 rows in set (0.00 sec)那我们再找出课程 MySQL 和 DBLE 里不及格的倒数前两名学生名单。
mysqlgtSELECT*FROM(,,,row_number () over (PARTITION BY BY ASC) score_rankingFROMstudent AS a,course AS b,score AS cWHERE IN (20192005, 20192001)AND lt 60) yttWHERE score_ranking lt 3
------- -------------- ------- --------------- | cname | sname | score | score_ranking | ------- -------------- ------- --------------- | mysql | 菠菜 | 40 | 1 || mysql | 杨发财 | 42 | 2 || dble | 武松 | 34 | 1 || dble | 东方不败 | 36 | 2 | ------- -------------- ------- --------------- 4 rows in set (0.00 sec)到此为止，我们只是演示了row_number() over() 函数的使用方法，其他的函数有兴趣的朋友可以自己体验体验，方法都差不多。