目录
前言
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;
展示结果如下:
胡思乱想成功(*^▽^*)