Mysql 行转列，列转行

1,创建表初始化数据CREATE TABLE StudentScores ( UserName varchar(20) DEFAULT NULL, Subject varchar(30) DEFAULT NULL, Score float DEFAULT NULL ) ENGINEInnoDB DEFAULT CHARSETutf8; 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) ) ENGINEINNODB AUTO_INCREMENT1 DEFAULT CHARSETutf8; 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) ) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8; 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;