初始化數(shù)據(jù)
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 '分數(shù)', `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, '小三', '數(shù)學', '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');
一、橫向轉(zhuǎn)縱向排列
SELECT user, SUM( CASE WHEN km = "語文" THEN fs ELSE 0 END ) "語文", SUM( CASE WHEN km = "數(shù)學" THEN fs ELSE 0 END ) "數(shù)學", SUM( CASE WHEN km = "英語" THEN fs ELSE 0 END ) "英語" FROM test_01 GROUP BY user
二、縱向轉(zhuǎn)橫向排列
SELECT km FROM test_01 WHERE id = 1 UNION SELECT fs FROM test_01 WHERE id = 1
到此這篇關(guān)于mysql橫向轉(zhuǎn)縱向、縱向轉(zhuǎn)橫向排列的方法的文章就介紹到這了,更多相關(guān)mysql橫向轉(zhuǎn)縱向、縱向轉(zhuǎn)橫向排列內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
標簽:惠州 呼和浩特 天津 合肥 沈陽 公主嶺 牡丹江 阿里
巨人網(wǎng)絡通訊聲明:本文標題《mysql橫向轉(zhuǎn)縱向、縱向轉(zhuǎn)橫向排列的方法》,本文關(guān)鍵詞 mysql,橫向,轉(zhuǎn),縱向,排列,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡,涉及言論、版權(quán)與本站無關(guān)。