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;