Mysql 行转列，列转行-蒲公英云

Mysql 行转列，列转行

小鱼儿 2022-02-19 07:27 556阅读 0赞

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

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)
)

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）概念比较清晰。

2、列转行

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;