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

60 阅读 0 评论 40 点赞

我是靠谱客的博主顺利鱼，最近开发中收集的这篇文章主要介绍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;