MySQL（case when用法、窗口函数（求每科成绩前三名）、行转列、列转行、连续登陆时长、统计表求中位数）

已于 2023-02-08 15:29:16 修改
阅读量7.9k 收藏 34
点赞数 1
分类专栏： 基础知识总结 文章标签： sql mysql 数据库
于 2021-11-24 21:34:11 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_48621755/article/details/121524860
版权

目录

前言

一、case when

二、窗口函数实现简单的成绩排名

扩展

三、行转列、列转行

行转列（以及窗口函数）

列转行

四、连续登陆时长

五、统计表求中位数 

 胡思乱想

前言

MySQL简单的应用，包括简单case when用法、简单开窗函数以及行转列以及列转行、连续登陆时间以及自己的胡思乱想；




一、case when

case when语法结构：

语法 :case a when b then c [when d then e] …… [else f] end

如果 a 等于 b ，那么返回 c ；如果 a 等于 d ，那么返回 e ；

否则返回 f

表结构如下：

DROP TABLE IF EXISTS `tb_case`;
CREATE TABLE `tb_case`  (
  `id` int NULL DEFAULT NULL,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `sex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `salary` int NULL DEFAULT NULL
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_case
-- ----------------------------
INSERT INTO `tb_case` VALUES (1, '万幸', '女', 2500);
INSERT INTO `tb_case` VALUES (2, '万岁', '男', 1500);
INSERT INTO `tb_case` VALUES (3, '时分', '男', 3500);
INSERT INTO `tb_case` VALUES (4, '时秒', '女', 1000);

SET FOREIGN_KEY_CHECKS = 1;

主要代码如下：

​#转换性别
#方法一
SELECT id, NAME, sex,
CASE WHEN sex="男" 
THEN "女"    ELSE "男" 
END AS sex_01 FROM tb_case;
#方法二
SELECT id, NAME, sex,
CASE sex WHEN "男" THEN "女"
ELSE "男" END AS sex_01 FROM tb_case;
#方法三
SELECT id, NAME, sex,
CASE sex="男" WHEN TRUE THEN "女"
ELSE "男" END AS sex_01 FROM tb_case;

运行结果如下：

对工资进行分级操作如下：

#工资分级
SELECT id, NAME, salary,
CASE salary >= 2500 
WHEN TRUE THEN "A" ELSE "B" 
END AS "等级" FROM tb_case;

SELECT id, NAME, salary,
CASE WHEN salary > 3000 THEN "A"  
WHEN salary BETWEEN 1500 AND 3000 THEN "B"
ELSE "C" END AS "等级" FROM tb_case;

运行结果如下：


 

二、窗口函数实现简单的成绩排名

窗口函数基础知识：

1）Rank

（1）RANK() 排序相同时会重复，总数不会变

（2）DENSE_RANK() 排序相同时会重复，总数会减少

（3）ROW_NUMBER() 会根据顺序计算

2） OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化

（1）CURRENT ROW：当前行

（2）n PRECEDING：往前n行数据

（3） n FOLLOWING：往后n行数据

（4）UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点

（5） LAG(col,n)：往前第n行数据

（6）LEAD(col,n)：往后第n行数据

（7） NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

结构如下：

DROP TABLE IF EXISTS `tb_course`;
CREATE TABLE `tb_course`  (
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `course` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `score` int NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_course
-- ----------------------------
INSERT INTO `tb_course` VALUES ('万幸', '数学', 98);
INSERT INTO `tb_course` VALUES ('万幸', '英语', 98);
INSERT INTO `tb_course` VALUES ('万幸', '语文', 76);
INSERT INTO `tb_course` VALUES ('万岁', '数学', 98);
INSERT INTO `tb_course` VALUES ('万岁', '语文', 87);
INSERT INTO `tb_course` VALUES ('万岁', '英语', 87);
INSERT INTO `tb_course` VALUES ('时分', '语文', 70);
INSERT INTO `tb_course` VALUES ('时分', '数学', 62);
INSERT INTO `tb_course` VALUES ('时分', '英语', 80);
INSERT INTO `tb_course` VALUES ('时秒', '语文', 99);
INSERT INTO `tb_course` VALUES ('时秒', '数学', 99);
INSERT INTO `tb_course` VALUES ('时秒', '英语', 95);

SET FOREIGN_KEY_CHECKS = 1;

主要运行代码如下

#开窗函数
#求每个学科的前三名
SELECT	* FROM	
( SELECT *, ROW_NUMBER() over ( PARTITION BY course ORDER BY score DESC ) AS 名次 FROM tb_course ) t WHERE 名次 <= 3;

运行结果如下：

扩展

MySQL是从8.0才开始支持开窗函数，不使用开窗函数实现成绩排名

#设置初值
SET @r = 0, @c = NULL;
#以课程和成绩排序后,手动赋予名次
SELECT NAME , course, score,
	@r := IF ( @c = course, @r + 1, 1 ) r,
	@c := course 
FROM tb_course 
ORDER BY course, score DESC

 运行结果如下：

三、行转列、列转行

行转列（以及窗口函数）

#行转列
SELECT NAME,
SUM(IF( `course` = "语文", score, 0 )) AS 语文,
SUM(IF( `course` = "数学", score, 0 )) AS 数学,
SUM(IF( `course` = "英语", score, 0 )) AS 英语,
SUM( score ) AS 总分
FROM tb_course GROUP BY NAME ORDER BY 总分 DESC;

#行转列并进行排名
SELECT *, ROW_NUMBER() OVER ( ORDER BY 总分 DESC ) AS 排名 FROM
(SELECT NAME,
SUM(IF( `course` = "语文", score, 0 )) AS 语文,
SUM(IF( `course` = "数学", score, 0 )) AS 数学,
SUM(IF( `course` = "英语", score, 0 )) AS 英语,
SUM( score ) AS 总分
FROM tb_course GROUP BY NAME) t;

SELECT *, RANK() OVER ( ORDER BY 总分 DESC ) AS 排名 FROM
(SELECT NAME,
SUM(IF( `course` = "语文", score, 0 )) AS 语文,
SUM(IF( `course` = "数学", score, 0 )) AS 数学,
SUM(IF( `course` = "英语", score, 0 )) AS 英语,
SUM( score ) AS 总分
FROM tb_course GROUP BY NAME) t;


#拼接字符串 
SELECT NAME,GROUP_CONCAT(`course`,":",score)AS 成绩 FROM tb_course
GROUP BY NAME;

结果展示：

列转行

表结构如下：

DROP TABLE IF EXISTS `tb_course01`;
CREATE TABLE `tb_course01`  (
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '名字',
  `chinese` double NULL DEFAULT NULL COMMENT '语文',
  `math` double NULL DEFAULT NULL COMMENT '数学',
  `English` double NULL DEFAULT NULL COMMENT '英语',
  PRIMARY KEY (`name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_course01
-- ----------------------------
INSERT INTO `tb_course01` VALUES ('万岁', 87, 98, 87);
INSERT INTO `tb_course01` VALUES ('万幸', 76, 98, 98);
INSERT INTO `tb_course01` VALUES ('时分', 70, 62, 80);
INSERT INTO `tb_course01` VALUES ('时秒', 99, 99, 95);

SET FOREIGN_KEY_CHECKS = 1;

源码数据展示：

 代码如下：

SELECT NAME,'语文' course,chinese AS score from tb_course01
UNION SELECT NAME,'数学' course,math AS score from tb_course01 
UNION SELECT NAME,'英语' course,English AS score from tb_course01
ORDER BY NAME,course

结果展示：

四、连续登陆时长

表结构如下：

DROP TABLE IF EXISTS `log`;
CREATE TABLE `log`  (
  `id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`, `date`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of log
-- ----------------------------
INSERT INTO `log` VALUES ('u0001', '2019-10-10');
INSERT INTO `log` VALUES ('u0001', '2019-10-11');
INSERT INTO `log` VALUES ('u0001', '2019-10-12');
INSERT INTO `log` VALUES ('u0001', '2019-10-14');
INSERT INTO `log` VALUES ('u0001', '2019-10-15');
INSERT INTO `log` VALUES ('u0001', '2019-10-17');
INSERT INTO `log` VALUES ('u0001', '2019-10-18');
INSERT INTO `log` VALUES ('u0001', '2019-10-19');
INSERT INTO `log` VALUES ('u0001', '2019-10-20');
INSERT INTO `log` VALUES ('u0002', '2019-10-20');

SET FOREIGN_KEY_CHECKS = 1;

SQL语句如下：

#连续登陆时间
SELECT id,COUNT( a ) u FROM
( SELECT id, DATE_ADD( date, INTERVAL row_number() over ( PARTITION BY id ORDER BY date DESC ) DAY ) AS a FROM log ) t 
GROUP BY id,a;

SELECT id,count( t ) u FROM
( SELECT *,( DAY ( date )-( ROW_NUMBER() over ( PARTITION BY id ORDER BY date ))) AS t FROM log ) k GROUP BY t;

运行结果如下：

五、统计表求中位数 

表结构如下：

-- ----------------------------
-- Table structure for tb_age
-- ----------------------------
DROP TABLE IF EXISTS `tb_age`;
CREATE TABLE `tb_age` (
  `age` int(11) NOT NULL,
  `age_cnt` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of tb_age
-- ----------------------------
BEGIN;
INSERT INTO `tb_age` (`age`, `age_cnt`) VALUES (1, 3);
INSERT INTO `tb_age` (`age`, `age_cnt`) VALUES (2, 1);
INSERT INTO `tb_age` (`age`, `age_cnt`) VALUES (3, 1);
INSERT INTO `tb_age` (`age`, `age_cnt`) VALUES (4, 4);
INSERT INTO `tb_age` (`age`, `age_cnt`) VALUES (5, 2);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

大致可以理解为年龄统计表来求中位数，即一岁的人有三位，两岁的有一位，三岁的有一位，四岁的有四位，五岁的有两位。正常排序应为 1 1 1 2 3 4 4 4 4 5 5一共十一个人，取中位数是4。

SQL语句如下：

#使用开窗函数计算统计表中位数
SELECT min( age ) 
FROM (  SELECT *, 
	sum( age_cnt ) over ( ORDER BY age ) AS n,
	( sum( age_cnt ) over ())/ 2 g 
	FROM tb_age 
) k WHERE n >=g

 运行结果如下：

 胡思乱想

在行转列的原始表是这个样子的

我想要在行转列的基础上改进，让查询可以显示出总分排名，并且在最后一行成功显示各科以及总分的平均分，毫无疑问需要用到union。可是查询到的字段不同，排名一列不需要显示，且名字一例想要显示平均分，在第二张表上补全字段，用‘平均分’代替name，用null代替排名，就有了：'平均分' as name以及null as 排名。最后查询出来的结果可以，但是小数点后有四位小数，对结果进行保留两位小数：convert(AVG(score),decimal(10,2))，需求全部满足，代码如下：

SELECT * , ROW_NUMBER() OVER ( ORDER BY 总分 DESC ) AS 排名 FROM
(SELECT name,
SUM(IF( `course` = "语文", score, 0 )) AS 语文,
SUM(IF( `course` = "数学", score, 0 )) AS 数学,
SUM(IF( `course` = "英语", score, 0 )) AS 英语,
SUM( score ) AS 总分
FROM tb_course GROUP BY NAME) t
UNION
SELECT '平均分' as name,
SUM(IF( `course` = "语文", score, 0 )) AS 语文,
SUM(IF( `course` = "数学", score, 0 )) AS 数学,
SUM(IF( `course` = "英语", score, 0 )) AS 英语,
SUM( score ) AS 总分,null as 排名
FROM(SELECT name,course, convert(AVG(score),decimal(10,2)) score FROM tb_course GROUP BY course) y;

 展示结果如下：

 胡思乱想成功(*^▽^*)

要藏好尾巴呦
关注
  • 1
    点赞
  • 踩
  • 34
    收藏
    觉得还不错? 一键收藏
  • 5
    评论
专栏目录
MySQL的CASE WHEN语句的几个使用实例
09-10
主要介绍了MySQL的CASE WHEN语句的几个使用实例,需要的朋友可以参考下
【MySQL】SQL之CASE WHEN用法详解
人类所有真实的快乐，一定是恒久的努力
10-18 5万+
SQL之CASE WHEN用法详解
MySQL(五）——常用函数和case when
weixin_58305115的博客
07-19 335
MySQL常用函数：字符函数、数学函数、时间和日期函数；流程操作：if函数，case when流程
MySQL 的CASE WHEN 语句使用说明
开心博客
09-29 5万+
介绍mysql数据库中case when语句的用法，首先介绍case when语句的基础知识，然后提供了相关例子。 （1）mysql数据库中CASE WHEN语句。 case when语句，用于计算条件列表并返回多个可能结果表达式之一。 CASE 具有两种格式：简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。 CASE 搜索函数计算一组布尔表达式以确定结果。 两种格式都支持可选的 ELSE 参数。 语法如下： 1）简单 CASE 函数： CASE input_expression WH
sql case when的用法（转）
weixin_43955984的博客
09-18 3736
简单CASE WHEN函数： CASE SCORE WHEN ‘A’ THEN ‘优’ ELSE ‘不及格’ END CASE SCORE WHEN ‘B’ THEN ‘良’ ELSE ‘不及格’ END CASE SCORE WHEN ‘C’ THEN ‘中’ ELSE ‘不及格’ END  等同于，使用CASE WHEN条件表达式函数实现： CASE WHEN SCORE = ‘A’...
sql语句之case when的用法
hubolu123的博客
01-27 6万+
应用场景： 当需要从数据源上直接判断数据显示带不动含义的时候，就可以在SQL语句中使用case when 函数。 语法如下： Case具有两种格式。简单Case函数和Case搜索函数。 简单Case函数格式： CASE 列名 WHEN 条件值1 THEN 选项1 WHEN 条件值2 THEN 选项2 …… ELSE 默认值 END （注释：列名，这里是sex，如果放在case后，则显示在屏幕上的列名即为列名sex） Case搜索函数： CASE WHEN 条件1 THEN 选项1 WHEN 条件2 THE
Case when的用法
热门推荐
mry6的博客
07-10 19万+
Case when 的用法: 一旦满足了某一个WHEN, 则这一条数据就会退出CASE WHEN , 而不再考虑 其他CASE; Case when 的用法 --   搜索Case函数: Case函数(Case搜索函数): 判断表达式的真假,如果为真,返回结果;如果为假,返回else值;如果未定义else值,则返回空值(使用条件确定返回值); select name,id,(case wh...
Mysql中的CASE WHEN
qq_44011569的博客
05-26 2456
在这个例子中，monitor_cover是一个字段，根据其值进行条件判断。如果monitor_cover的值为1，条件成立，返回字符串’已监控’作为name列的值；否则，条件不成立，返回字符串’未监控’作为name列的值。其中，condition1、condition2等是条件表达式，可以是列比较、逻辑运算或函数调用等。语句被用于根据monitor_cover字段的值进行条件判断，并返回相应的名称。是一种条件表达式，用在查询语句中进行条件判断和返回不同的结果。表达式，可以根据字段值的不同返回不同的结果。
mysql中case when的用法
大数据
05-01 7万+
case when的使用情况有三种 等值转换 范围转换 列转行操作 1.等值转换 设计数据库的时候总会把用户的性别用int存储('0’为女，'1’为男)，但是怎么把它抓换成汉字显示呢？ select name as '姓名', (case sex when 0 then '女' else '男' end) as '性别' from test.student; 查询结果： 2.范围转换 按照用户成绩显示优（90分以上）、良（80分-90分）、及格（60分-80分）、不及格（60
MySQL中的CASE WHEN语句：用法、示例与解析
wenxuankeji的博客
02-25 1万+
MySQL中的CASE WHEN语句：用法、示例与解析
mysql case when 用法
weixin_44487203的博客
05-16 3万+
开发中经常用到，但是老是忘记，简单记一下 CASE语句为多分支语句结构，该语句首先从WHEN后的VALUE中查找与CASE后的VALUE相等的值，如果查找到，则执行该分支的内容，否则执行ELSE后面的内容。 mysql case when 有两种用法 1、简单函数 CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END 枚举这个字段所有可能的值。 2、搜索函数 CASE WHEN [expr] THEN [result1]…ELSE [
MySQL知识汇总：MySQL函数CASE WHEN用法详解
七叔的博客
02-19 2万+
本文用于探讨MySQL函数CASE WHEN用法详解
SQL语法之CASE WHEN的使用总结
本人的技术和文笔有限，正在努力成长中，请各位朋友多多指正与包涵！
06-23 1万+
如果 column1 的值为 ‘B’，则筛选 column2 小于 20 的数据；在这个语法中，CASE 表示开始一个 CASE WHEN 表达式，WHEN 表示一个条件分支，THEN 表示条件成立时返回的结果，ELSE 表示所有分支都不成立时返回的默认结果，END 表示结束整个 CASE WHEN 表达式。在这种语法中，column_name 表示要进行分类或分段处理的列名，value 表示要匹配的数值，result 表示匹配成功时返回的结果，ELSE 表示所有分支都不匹配时返回的默认结果。
MySQL中CASE WHEN用法总结
聚娃科技开发者博客
06-22 743
大家好，我是免费搭建查券返利机器人省钱赚佣金就用微赚淘客系统3.0的小编，也是冬天不穿秋裤，天冷也要风度的程序猿！在MySQL中，CASE WHEN语句是一种条件表达式，用于在查询中进行条件判断和逻辑分支控制。它类似于编程语言中的switch语句，可以根据条件的不同执行不同的逻辑。
MySql中case when的用法
路飞船长的博客
01-16 3757
CASE 具有两种格式：简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。CASE 搜索函数计算一组布尔表达式以确定结果。在 SELECT 语句中，CASE 搜索函数允许根据比较值在结果集内对值进行替换。在 SELECT 语句中，简单 CASE 函数仅检查是否相等，而不进行其它比较。2、使用带有简单 CASE 函数和 CASE 搜索函数的SELECT 语句。case when语句，用于计算条件列表并返回多个可能结果表达式之一。1、 使用带有简单 CASE 函数的 SELECT 语句。
MySql中Case-When的几种用法
qq_38394848的博客
10-12 1万+
一．MySql中Case-When的几种用法 注：本文章例子来自其他博客，整理后发出的。 原文章例子地址：http://www.cnblogs.com/clphp/p/6256207.html 1.语法 严格来讲不应该叫“mysql条件判断语句case when”他的专业术语应该叫“mysql 流程控制语句case语法” 用法1： case变量 CASE case_value WHEN when...
MySQL中的case when使用
qinAction的博客
11-12 4503
Mysql中case when语法： 语法一：case_value是变量或者表达式（总而言之case_value必须是一个值例如：1,2,3） CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE 语法一实例查询： 语法二：CASE后面不需要变量或者表达式，直接执行每一.
SQL中case when用法详解及使用案例
weixin_41979002的博客
03-23 1万+
Case具有两种格式。简单Case函数和Case搜索函数。
mysql case when 后行转列
最新发布
08-06
在MySQL中，CASE WHEN语句用于根据特定条件对一列或多列的数据进行条件判断并返回不同的值。如果想要将一行数据转化为多列（即所谓的"列转行"或"扁平化"操作），你可以使用UNION ALL和CASE WHEN的组合。这里是一个简单的例子： ```sql SELECT CASE WHEN condition_1 THEN 'value_1' WHEN condition_2 THEN 'value_2' ELSE 'default_value' END AS new_column_name, another_column FROM table_name UNION ALL ( SELECT CASE WHEN condition_1 THEN 'value_1' WHEN condition_2 THEN 'value_2' ELSE 'default_value' END AS new_column_name, another_column FROM table_name WHERE some_additional_condition ) ``` 在这个示例中，`condition_1`, `condition_2`是针对`another_column`的判断条件，`new_column_name`是你希望创建的新列名，`table_name`是你的表名，`some_additional_condition`是在第二个UNION ALL子查询中额外添加的条件。 这个查询的结果会包含满足任一条件后的行，每个条件对应一个新的列。如果你有多个可能的取值，你需要列出所有可能的情况。
评论 5
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值