mysql 行转列

数据准备
/** 学生表*/
CREATE TABLE tb_student(id INT,NAME VARCHAR(20));
/** 课程表  */
CREATE TABLE tb_course(id INT ,NAME VARCHAR(20));
/** 成绩表  */
CREATE TABLE tb_grade(s_id INT,c_id INT ,greade INT);

INSERT INTO tb_student(id,NAME) VALUES(1,'小明'),(2,'小马'),(3,'小朱'),(4,'小张');
INSERT INTO tb_course(id,NAME) VALUES(1,'语文'),(2,'数学'),(3,'英语'),(4,'美术');

INSERT INTO tb_grade(s_id,c_id,greade) VALUES
(1,1,80),(1,2,81),(1,3,82),(1,4,83),
(2,1,90),(2,2,91),(2,3,92),(2,4,93),
(3,1,70),(3,2,71),(3,3,72),(3,4,73),
(4,1,80),(4,2,81),(4,3,82),(4,4,83);

数据为：

行转列sql

SELECT 

    s.NAME,

    MAX(IF(c.NAME='语文',greade,0)) AS '语文',

    MAX(IF(c.NAME='数学',greade,0)) AS '数学',

    MAX(IF(c.NAME='英语',greade,0)) AS '英语',

    MAX(IF(c.NAME='美术',greade,0)) AS '美术'

FROM tb_student s

LEFT JOIN tb_grade g ON s.id = g.s_id

LEFT JOIN tb_course c ON g.c_id = c.id

GROUP BY s.NAME;

转换后数据展示：

计算学生的总成绩

SELECT  s.NAME,  SUM(g.greade) 
FROM   tb_student s 
       LEFT JOIN   tb_grade g   ON s.id = g.s_id 
       LEFT JOIN  tb_course c   ON c.id = g.c_id 
GROUP BY s.NAME ;

总成绩行转列

SELECT SUM(CASE WHEN NAME='小张' THEN gread END) AS '小张' ,
SUM(CASE WHEN NAME='小明' THEN gread END) AS '小明' ,
SUM(CASE WHEN NAME='小朱' THEN gread END) AS '小朱' ,
SUM(CASE WHEN NAME='小马' THEN gread END) AS '小马' 
FROM (
SELECT 
  s.NAME AS NAME,
  SUM(g.greade) AS gread
FROM
  tb_student s   LEFT JOIN  tb_grade g   ON s.id = g.s_id 
  LEFT JOIN  tb_course c   ON c.id = g.c_id 
GROUP BY s.NAME 
) t;

结果数据展示

动态行转列：http://www.cnblogs.com/emanlee/p/3550149.html