devbox
知新_RL
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

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

作者：知新_RL | 2024-04-25 12:20:49

赞

踩

mysql case when

目录

前言

一、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

表结构如下：

  1. DROP TABLE IF EXISTS `tb_case`;
  2. CREATE TABLE `tb_case`  (
  3.   `id` int NULL DEFAULT NULL,
  4.   `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  5.   `sex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  6.   `salary` int NULL DEFAULT NULL
  7. ) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  8.  
  9. -- ----------------------------
  10. -- Records of tb_case
  11. -- ----------------------------
  12. INSERT INTO `tb_case` VALUES (1, '万幸', '女', 2500);
  13. INSERT INTO `tb_case` VALUES (2, '万岁', '男', 1500);
  14. INSERT INTO `tb_case` VALUES (3, '时分', '男', 3500);
  15. INSERT INTO `tb_case` VALUES (4, '时秒', '女', 1000);
  16.  
  17. SET FOREIGN_KEY_CHECKS = 1;

主要代码如下：

  1. ​#转换性别
  2. #方法一
  3. SELECT id, NAME, sex,
  4. CASE WHEN sex="男" 
  5. THEN "女"    ELSE "男" 
  6. END AS sex_01 FROM tb_case;
  7. #方法二
  8. SELECT id, NAME, sex,
  9. CASE sex WHEN "男" THEN "女"
  10. ELSE "男" END AS sex_01 FROM tb_case;
  11. #方法三
  12. SELECT id, NAME, sex,
  13. CASE sex="男" WHEN TRUE THEN "女"
  14. ELSE "男" END AS sex_01 FROM tb_case;

运行结果如下：

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

  1. #工资分级
  2. SELECT id, NAME, salary,
  3. CASE salary >= 2500 
  4. WHEN TRUE THEN "A" ELSE "B" 
  5. END AS "等级" FROM tb_case;
  6.  
  7. SELECT id, NAME, salary,
  8. CASE WHEN salary > 3000 THEN "A"  
  9. WHEN salary BETWEEN 1500 AND 3000 THEN "B"
  10. 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类型。

结构如下：

  1. DROP TABLE IF EXISTS `tb_course`;
  2. CREATE TABLE `tb_course`  (
  3.   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  4.   `course` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  5.   `score` int NULL DEFAULT NULL
  6. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  7.  
  8. -- ----------------------------
  9. -- Records of tb_course
  10. -- ----------------------------
  11. INSERT INTO `tb_course` VALUES ('万幸', '数学', 98);
  12. INSERT INTO `tb_course` VALUES ('万幸', '英语', 98);
  13. INSERT INTO `tb_course` VALUES ('万幸', '语文', 76);
  14. INSERT INTO `tb_course` VALUES ('万岁', '数学', 98);
  15. INSERT INTO `tb_course` VALUES ('万岁', '语文', 87);
  16. INSERT INTO `tb_course` VALUES ('万岁', '英语', 87);
  17. INSERT INTO `tb_course` VALUES ('时分', '语文', 70);
  18. INSERT INTO `tb_course` VALUES ('时分', '数学', 62);
  19. INSERT INTO `tb_course` VALUES ('时分', '英语', 80);
  20. INSERT INTO `tb_course` VALUES ('时秒', '语文', 99);
  21. INSERT INTO `tb_course` VALUES ('时秒', '数学', 99);
  22. INSERT INTO `tb_course` VALUES ('时秒', '英语', 95);
  23.  
  24. SET FOREIGN_KEY_CHECKS = 1;

主要运行代码如下

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

运行结果如下：

扩展

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

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

 运行结果如下：

三、行转列、列转行

行转列（以及窗口函数）

  1. #行转列
  2. SELECT NAME,
  3. SUM(IF( `course` = "语文", score, 0 )) AS 语文,
  4. SUM(IF( `course` = "数学", score, 0 )) AS 数学,
  5. SUM(IF( `course` = "英语", score, 0 )) AS 英语,
  6. SUM( score ) AS 总分
  7. FROM tb_course GROUP BY NAME ORDER BY 总分 DESC;
  8.  
  9. #行转列并进行排名
  10. SELECT *, ROW_NUMBER() OVER ( ORDER BY 总分 DESC ) AS 排名 FROM
  11. (SELECT NAME,
  12. SUM(IF( `course` = "语文", score, 0 )) AS 语文,
  13. SUM(IF( `course` = "数学", score, 0 )) AS 数学,
  14. SUM(IF( `course` = "英语", score, 0 )) AS 英语,
  15. SUM( score ) AS 总分
  16. FROM tb_course GROUP BY NAME) t;
  17.  
  18. SELECT *, RANK() OVER ( ORDER BY 总分 DESC ) AS 排名 FROM
  19. (SELECT NAME,
  20. SUM(IF( `course` = "语文", score, 0 )) AS 语文,
  21. SUM(IF( `course` = "数学", score, 0 )) AS 数学,
  22. SUM(IF( `course` = "英语", score, 0 )) AS 英语,
  23. SUM( score ) AS 总分
  24. FROM tb_course GROUP BY NAME) t;
  25.  
  26.  
  27. #拼接字符串 
  28. SELECT NAME,GROUP_CONCAT(`course`,":",score)AS 成绩 FROM tb_course
  29. GROUP BY NAME;

结果展示：

列转行

表结构如下：

  1. DROP TABLE IF EXISTS `tb_course01`;
  2. CREATE TABLE `tb_course01`  (
  3.   `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '名字',
  4.   `chinese` double NULL DEFAULT NULL COMMENT '语文',
  5.   `math` double NULL DEFAULT NULL COMMENT '数学',
  6.   `English` double NULL DEFAULT NULL COMMENT '英语',
  7.   PRIMARY KEY (`name`) USING BTREE
  8. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  9.  
  10. -- ----------------------------
  11. -- Records of tb_course01
  12. -- ----------------------------
  13. INSERT INTO `tb_course01` VALUES ('万岁', 87, 98, 87);
  14. INSERT INTO `tb_course01` VALUES ('万幸', 76, 98, 98);
  15. INSERT INTO `tb_course01` VALUES ('时分', 70, 62, 80);
  16. INSERT INTO `tb_course01` VALUES ('时秒', 99, 99, 95);
  17.  
  18. SET FOREIGN_KEY_CHECKS = 1;

源码数据展示：

 代码如下：

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

结果展示：

四、连续登陆时长

表结构如下：

  1. DROP TABLE IF EXISTS `log`;
  2. CREATE TABLE `log`  (
  3.   `id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  4.   `date` date NOT NULL,
  5.   PRIMARY KEY (`id`, `date`) USING BTREE
  6. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
  7.  
  8. -- ----------------------------
  9. -- Records of log
  10. -- ----------------------------
  11. INSERT INTO `log` VALUES ('u0001', '2019-10-10');
  12. INSERT INTO `log` VALUES ('u0001', '2019-10-11');
  13. INSERT INTO `log` VALUES ('u0001', '2019-10-12');
  14. INSERT INTO `log` VALUES ('u0001', '2019-10-14');
  15. INSERT INTO `log` VALUES ('u0001', '2019-10-15');
  16. INSERT INTO `log` VALUES ('u0001', '2019-10-17');
  17. INSERT INTO `log` VALUES ('u0001', '2019-10-18');
  18. INSERT INTO `log` VALUES ('u0001', '2019-10-19');
  19. INSERT INTO `log` VALUES ('u0001', '2019-10-20');
  20. INSERT INTO `log` VALUES ('u0002', '2019-10-20');
  21.  
  22. SET FOREIGN_KEY_CHECKS = 1;

SQL语句如下：

  1. #连续登陆时间
  2. SELECT id,COUNT( a ) u FROM
  3. ( SELECT id, DATE_ADD( date, INTERVAL row_number() over ( PARTITION BY id ORDER BY date DESC ) DAY ) AS a FROM log ) t 
  4. GROUP BY id,a;
  5.  
  6. SELECT id,count( t ) u FROM
  7. ( SELECT *,( DAY ( date )-( ROW_NUMBER() over ( PARTITION BY id ORDER BY date ))) AS t FROM log ) k GROUP BY t;

运行结果如下：

五、统计表求中位数 

表结构如下：

  1. -- ----------------------------
  2. -- Table structure for tb_age
  3. -- ----------------------------
  4. DROP TABLE IF EXISTS `tb_age`;
  5. CREATE TABLE `tb_age` (
  6.   `age` int(11) NOT NULL,
  7.   `age_cnt` int(11) DEFAULT NULL
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  9.  
  10. -- ----------------------------
  11. -- Records of tb_age
  12. -- ----------------------------
  13. BEGIN;
  14. INSERT INTO `tb_age` (`age`, `age_cnt`) VALUES (1, 3);
  15. INSERT INTO `tb_age` (`age`, `age_cnt`) VALUES (2, 1);
  16. INSERT INTO `tb_age` (`age`, `age_cnt`) VALUES (3, 1);
  17. INSERT INTO `tb_age` (`age`, `age_cnt`) VALUES (4, 4);
  18. INSERT INTO `tb_age` (`age`, `age_cnt`) VALUES (5, 2);
  19. COMMIT;
  20.  
  21. SET FOREIGN_KEY_CHECKS = 1;

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

SQL语句如下：

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

 运行结果如下：

 胡思乱想

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

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

  1. SELECT * , ROW_NUMBER() OVER ( ORDER BY 总分 DESC ) AS 排名 FROM
  2. (SELECT name,
  3. SUM(IF( `course` = "语文", score, 0 )) AS 语文,
  4. SUM(IF( `course` = "数学", score, 0 )) AS 数学,
  5. SUM(IF( `course` = "英语", score, 0 )) AS 英语,
  6. SUM( score ) AS 总分
  7. FROM tb_course GROUP BY NAME) t
  8. UNION
  9. SELECT '平均分' as name,
  10. SUM(IF( `course` = "语文", score, 0 )) AS 语文,
  11. SUM(IF( `course` = "数学", score, 0 )) AS 数学,
  12. SUM(IF( `course` = "英语", score, 0 )) AS 英语,
  13. SUM( score ) AS 总分,null as 排名
  14. FROM(SELECT name,course, convert(AVG(score),decimal(10,2)) score FROM tb_course GROUP BY course) y;

 展示结果如下：

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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/知新_RL/article/detail/485232
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号