Mysql 行转列，列转行

1,创建表初始化数据

CREATE TABLE `StudentScores` ( `UserName` varchar(20) DEFAULT NULL, `Subject` varchar(30) DEFAULT NULL, `Score` float DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO StudentScores SELECT 'Nick', '语文', 80; INSERT INTO StudentScores SELECT 'Nick', '数学', 90; INSERT INTO StudentScores SELECT 'Nick', '英语', 70; INSERT INTO StudentScores SELECT 'Nick', '生物', 85; INSERT INTO StudentScores SELECT 'Kent', '语文', 80; INSERT INTO StudentScores SELECT 'Kent', '数学', 90; INSERT INTO StudentScores SELECT 'Kent', '英语', 70; INSERT INTO StudentScores SELECT 'Kent', '生物', 85;

2，查询行转列

如果我想知道每位学生的每科成绩，而且每个学生的全部成绩排成一行，这样方便我查看、统计，导出数据

SELECT UserName, MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文', MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '数学', MAX(CASE Subject WHEN '英语' THEN Score ELSE 0 END) AS '英语', MAX(CASE Subject WHEN '生物' THEN Score ELSE 0 END) AS '生物' FROM StudentScores GROUP BY UserName

查询结果如图所示，这样我们就能很清楚的了解每位学生所有的成绩了

CREATE TABLE Inpours ( ID INT , UserName NVARCHAR(20), --游戏玩家 CreateTime DATETIME, --充值时间 PayType NVARCHAR(20), --充值类型 Money DECIMAL, --充值金额 IsSuccess BIT, --是否成功 1表示成功， 0表示失败 CONSTRAINT PK_Inpours_ID PRIMARY KEY(ID) ) CREATE TABLE `Inpours` ( `ID` INT(10) NOT NULL AUTO_INCREMENT, `UserName` VARCHAR(20) DEFAULT NULL, `CreateTime` DATETIME, `PayType` VARCHAR(20) DEFAULT NULL, `Money` DECIMAL, `IsSuccess` BIT, CONSTRAINT PK_Inpours_ID PRIMARY KEY (`ID`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; INSERT INTO Inpours SELECT '1','张三', '2010-05-01', '支付宝', 50, 1; INSERT INTO Inpours SELECT '2','张三', '2010-06-14', '支付宝', 50, 1; INSERT INTO Inpours SELECT '3','张三', '2010-06-14', '手机短信', 100, 1; INSERT INTO Inpours SELECT '4','李四', '2010-06-14', '手机短信', 100, 1; INSERT INTO Inpours SELECT '5','李四', '2010-07-14', '支付宝', 100, 1; INSERT INTO Inpours SELECT '6','王五', '2010-07-14', '工商银行卡', 100, 1; INSERT INTO Inpours SELECT '7','赵六', '2010-07-14', '建设银行卡', 100, 1;

下面来了一个统计数据的需求，要求按日期、支付方式来统计充值金额信息。这也是一个典型的行转列的例子。我们可以通过下面的脚本来达到目的

SELECT CreateTime, (CASE PayType WHEN '支付宝' THEN SUM(Money) ELSE 0 END) AS '支付宝', (CASE PayType WHEN '手机短信' THEN SUM(Money) ELSE 0 END) AS '手机短信', (CASE PayType WHEN '工商银行卡' THEN SUM(Money) ELSE 0 END) AS '工商银行卡', (CASE PayType WHEN '建设银行卡' THEN SUM(Money) ELSE 0 END) AS '建设银行卡' FROM Inpours GROUP BY CreateTime,PayType

其实行转列，关键是要理清逻辑，而且对分组（Group by）概念比较清晰。

3、列转行

CREATE TABLE `TEST_TB_GRADE2` ( `ID` int(10) NOT NULL AUTO_INCREMENT, `USER_NAME` varchar(20) DEFAULT NULL, `CN_SCORE` float DEFAULT NULL, `MATH_SCORE` float DEFAULT NULL, `EN_SCORE` float DEFAULT '0', PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; insert into TEST_TB_GRADE2(USER_NAME, CN_SCORE, MATH_SCORE, EN_SCORE) values ("张三", 34, 58, 58), ("李四", 45, 87, 45), ("王五", 76, 34, 89); select user_name, '语文' COURSE , CN_SCORE as SCORE from TEST_TB_GRADE2 union select user_name, '数学' COURSE, MATH_SCORE as SCORE from TEST_TB_GRADE2 union select user_name, '英语' COURSE, EN_SCORE as SCORE from TEST_TB_GRADE2 order by user_name,COURSE;