mysql 行列互换，列列互换数据，数组列转多行数据

1. 列变行，比较难，需要使用存储过程，调用动态sql，使用mysql的系统表information_schema.`COLUMNS`。


2.行变列，比较简单。

2.1 sum if
SELECT
    cname AS "姓名",
    SUM(IF(cource="语文",score,0)) AS "语文",
    SUM(IF(cource="数学",score,0)) AS "数学",
    SUM(IF(cource="物理",score,0)) AS "物理",
    SUM(score) AS "总成绩",
    AVG(score) AS "平均成绩"
FROM tb
GROUP BY cname

2.2 sum if 加汇总列
(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' AS 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)

2.3 case when + sum汇总列
SELECT
  cname AS "姓名",
  MAX(CASE cource WHEN "语文" THEN score ELSE 0 END) AS "语文",
  MAX(CASE cource WHEN "数学" THEN score ELSE 0 END) AS "数学",
  MAX(CASE cource WHEN "物理" THEN score ELSE 0 END) AS "物理",
  SUM(score) AS "总成绩",
  ROUND(AVG(score) ,2) AS "平均成绩"
FROM tb
GROUP BY `cname`;

2.4 sum if + WITH rollup 汇总列
SELECT
  IFNULL(c1, 'total') AS A列,
    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.5 sum if + with rollup 不用子查询
select
ifnull(c1,'total') As A,
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 ;

2.6 动态sql
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']' from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'  exec(@sql1)
或
SET @EE='';
SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=\'',C2,'\'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A;
SET @QQ=CONCAT('SELECT ifnull(c1,\'total\') AS A,',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;

3. mysql 两列数据互换
update product as a, product as b set a.original_price=b.price, a.price=b.original_price where a.id=b.id;

4. mysql 根据数组列插入多行
select a.ID,substring_index(substring_index(a.mSize,',',b.AutoIncreID),',',-1)
from
tbl_name a
join
incre_table b
on b.AutoIncreID <= (length(a.mSize) - length(replace(a.mSize,',',''))+1)
order by a.ID;