SQL行转列

select name,
max(IF(subject ='chinese',score,0)) as 'chinese',
max(IF(subject ='math',score,0)) as 'math',
max(IF(subject ='english',score,0)) as 'english',
sum(score) as 'total'
from test_user
group by name

 

 

select name,

sum(case subject when '数学' then source else 0 end) as '数学',

sum(case subject when '英语' then source else 0 end) as '英语',

sum(case subject when '语文' then source else 0 end) as '语文'    

from   test   

group   by   name

 

 

 

分析执行计划：

1、对表中数据根据name进行分组

2、对分组后的数据，进行处理。

 

 

---

利用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行,并利用 IFNULL将汇总行标题显示为 Tota

 

mysql> SELECT
    ->     IFNULL(c1,'total') AS total,
    ->     SUM(IF(c2='B1',c3,0)) AS B1,
    ->     SUM(IF(c2='B2',c3,0)) AS B2,
    ->     SUM(IF(c2='B3',c3,0)) AS B3,
    ->     SUM(IF(c2='B4',c3,0)) AS B4,
    ->     SUM(IF(c2='total',c3,0)) AS total
    -> FROM (
    ->     SELECT c1,IFNULL(c2,'total') AS c2,SUM(c3) AS c3
    ->     FROM tx
    ->     GROUP BY c1,c2
    ->     WITH ROLLUP
    ->     HAVING c1 IS NOT NULL
    -> ) AS A
    -> GROUP BY c1
    -> WITH ROLLUP;

 

 

 

2. 利用SUM(IF()) 生成列 + UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total
mysql> select c1,
    -> sum(if(c2='B1',C3,0)) AS B1,
    -> sum(if(c2='B2',C3,0)) AS B2,
    -> sum(if(c2='B3',C3,0)) AS B3,
    -> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL
    -> from tx
    -> group by C1
    -> UNION
    -> SELECT 'TOTAL',sum(if(c2='B1',C3,0)) AS B1,
    -> sum(if(c2='B2',C3,0)) AS B2,
    -> sum(if(c2='B3',C3,0)) AS B3,
    -> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) FROM TX
    -> ;

 

 

3.  利用SUM(IF()) 生成列,直接生成结果不再利用子查询
mysql> select ifnull(c1,'total'),
    -> sum(if(c2='B1',C3,0)) AS B1,
    -> sum(if(c2='B2',C3,0)) AS B2,
    -> sum(if(c2='B3',C3,0)) AS B3,
    -> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL
    -> from tx
    -> group by C1 with rollup ;

 

 

mysql> SET @EE='';
mysql> SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=\'',C2,'\'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A;

 

mysql> SET @QQ=CONCAT('SELECT ifnull(c1,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt2 FROM @QQ;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt2;

 

 

---

SELECTDISTINCT  a.name,

(SELECTscore FROMgrade b WHEREa.name=b.nameANDb.course='语文') AS'语文',

(SELECTscore FROMgrade b WHEREa.name=b.nameANDb.course='数学') AS'数学',

(SELECTscore FROMgrade b WHEREa.name=b.nameANDb.course='英语') AS'英语'

FROMgrade a

 

SELECTname,

SUM(CASE  course WHEN  '语文'THENscore END) AS'语文',

SUM(CASE  course WHEN  '数学'THENscore END) AS'数学',

SUM(CASE  course WHEN  '英语'THENscore END) AS'英语'

FROMgrade GROUPBYname

 

 

DELIMITER &&

CREATEPROCEDUREsp_count()

BEGIN

#课程名称

DECLAREcourse_n VARCHAR(20);

#所有课程数量

DECLAREcountINT;

#计数器

DECLAREi INTDEFAULT0;

#拼接SQL字符串

SET@s = 'SELECT name';

SETcount= (SELECT  COUNT(distinctcourse) FROMgrade);

WHILE i < countDO

SETcourse_n = (SELECTcourse FROMgrade LIMIT i,1);

SET@s = CONCAT(@s, ', SUM(CASE  course WHEN  ','\'', course_n,'\'',' THEN score END )',' AS ','\'',course_n,'\'');

SETi = i+1;

ENDWHILE;

SET@s = CONCAT(@s, ' FROM grade GROUP BY name');

#用于调试

#SELECT@s;

PREPAREstmt FROM@s;

EXECUTEstmt;

END

&&

 

call sp_count();

 

 

第一种方法使用了表连接。
第二种使用了分组，对每个分组分别处理。
第三种使用了存储过程，其实是第二种方法的动态化，先计算出所有课程的数量，然后对每个分组进行课程查询。
很明显前两种方法属于硬编码，增加课程后就需要修改SQL。而第三种则没有这种问题。

 

 

MySQL基本命令【1】