mysql 横向排列,mysql横向转纵向、纵向转横向排列的方法

初始化数据

DROP TABLE IF EXISTS `test_01`;

CREATE TABLE `test_01` (

`id` int(0) NOT NULL,

`user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户',

`km` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '科目',

`fs` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '分数',

`time` datetime(0) NULL DEFAULT NULL COMMENT '时间',

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `test_01` VALUES (1, '小三', '语文', '98', '2020-08-06 15:51:21');

INSERT INTO `test_01` VALUES (2, '小三', '数学', '90', '2020-07-01 15:51:25');

INSERT INTO `test_01` VALUES (3, '小三', '英语', '77', '2020-06-01 15:51:28');

INSERT INTO `test_01` VALUES (4, '小二', '英语', '78', '2020-06-01 15:51:28');

一、横向转纵向排列

SELECT

user,

SUM( CASE WHEN km = "语文" THEN fs ELSE 0 END ) "语文",

SUM( CASE WHEN km = "数学" THEN fs ELSE 0 END ) "数学",

SUM( CASE WHEN km = "英语" THEN fs ELSE 0 END ) "英语"

FROM

test_01

GROUP BY user

二、纵向转横向排列

SELECT km FROM test_01 WHERE id = 1

UNION

SELECT fs FROM test_01 WHERE id = 1

总结

以上是生活随笔为你收集整理的mysql 横向排列,mysql横向转纵向、纵向转横向排列的方法的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。