初始化數據

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横向转纵向、纵向转横向排列的方法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。