mysql 将列值转变为列的例子

应同学的请求，写个将列值转变为列的sql语句，记录一下。

-- 创建库
CREATE TABLE `rate` (
 `uname` VARCHAR (300),
 `object` VARCHAR (300),
 `score` VARCHAR (300)
);

-- 插入数据
INSERT INTO test.rate (uname, object, score)
 VALUES('aaa', 'chinese', '67'),
 ('aaa', 'math', '89'),
 ('aaa', 'physical', '89'),
 ('bbb', 'chinese', '67'),
 ('bbb', 'math', '75'),
 ('bbb', 'physical', '89');

-- 查询
SELECT DISTINCT uname AS '姓名',
SUM(CASE object
WHEN 'chinese' THEN score END)  AS '语文',
SUM(CASE object
WHEN 'math' THEN score END)  AS '数学',
SUM(CASE object
WHEN 'physical' THEN score END)  AS '物理' FROM rate GROUP BY uname;

转载于:https://www.cnblogs.com/natgeo/archive/2013/05/06/3063888.html