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;
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;