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 '用戶(hù)',
`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 '分?jǐn)?shù)',
`time` datetime(0) NULL DEFAULT NULL COMMENT '時(shí)間',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
INSERT INTO `test_01` VALUES (1, '小三', '語(yǔ)文', '98', '2020-08-06 15:51:21');
INSERT INTO `test_01` VALUES (2, '小三', '數(shù)學(xué)', '90', '2020-07-01 15:51:25');
INSERT INTO `test_01` VALUES (3, '小三', '英語(yǔ)', '77', '2020-06-01 15:51:28');
INSERT INTO `test_01` VALUES (4, '小二', '英語(yǔ)', '78', '2020-06-01 15:51:28');
SELECT
user,
SUM( CASE WHEN km = "語(yǔ)文" THEN fs ELSE 0 END ) "語(yǔ)文",
SUM( CASE WHEN km = "數(shù)學(xué)" THEN fs ELSE 0 END ) "數(shù)學(xué)",
SUM( CASE WHEN km = "英語(yǔ)" THEN fs ELSE 0 END ) "英語(yǔ)"
FROM
test_01
GROUP BY user
到此這篇關(guān)于mysql橫向轉(zhuǎn)縱向、縱向轉(zhuǎn)橫向排列的方法的文章就介紹到這了,更多相關(guān)mysql橫向轉(zhuǎn)縱向、縱向轉(zhuǎn)橫向排列內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
