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
到此這篇關于mysql橫向轉縱向、縱向轉橫向排列的方法的文章就介紹到這了,更多相關mysql橫向轉縱向、縱向轉橫向排列內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！
