mysql 将列值转变为列的方法

274 阅读 0 评论 181 点赞

我是靠谱客的博主生动故事，这篇文章主要介绍mysql 将列值转变为列的方法，现在分享给大家，希望可以做个参考。

复制代码代码如下:

-- 创建库
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;