SQL/MYSQL /将表/列转置成行，行和作为新标题的列

Question

我有一个这样的表

A    B    C
1    4    7
2    5    8
3    6    9
And i want result like this

Columns     Values
A           sum(A) = 6 
B           sum(B) = 15
C           sum(C) = 24

在Excel表格中很简单，但我在MySql中卡住了谢谢你的帮助由于

—SeasonType,Sacks,SacksYards是列

select SeasonType,
MAX(IF(SeasonType = '1',  Sacks, null)) AS 'Q1',
MAX (IF(SeasonType = '1',  SacksYards, null)) AS 'Q2' 
from t3 GROUP BY SeasonType

——union all attempt column sacks,sacksyards table————fantasydefencegame

select 'Sacks' as Sacks, 'SackYards' as SackYards, 0 as SortOrder
union all
select Sum(Sacks)  total from fantasydefensegame
union
select Sum(SackYards) from fantasydefensegame
union
select sum(PointsAllowed) from fantasydefensegame
group by SeasonType

select sum(Sacks) sacks from t3 
union all
select sum(SackYards) sackyards from t3 group by SeasonType

**-- Another rough Attempt on Temp table**
Select sum(Sacks),sum(Sackyards) from t5 
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when Season = '2009' ''',
Season,
''' then field_value end) ',
Season
)
) INTO @sql
FROM
t5;
SET @sql = CONCAT('SELECT Sacks, ', @sql, ' 
FROM t5 
GROUP BY Season');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Answer 1

这应该会给你一些想法。假设我们使用一个名为testdb的测试数据库，您的原始表名为test，它有3列，即a,b,c。表中的三行与您之前提供的一样。接下来，我们可以继续创建一个存储过程。注意:使用预处理语句获取每个列的和值的原因是列名必须硬编码的规则，不能用变量替换。例如:select sum(a) from test;不能写成select sum(@column_name) from test;。通过使用预处理语句，我们可以动态地硬编码列名。

delimiter //
drop procedure if exists table_sum//
create procedure table_sum (db_name varchar(20),tb_name varchar(20))
begin
declare col_name varchar(10);
declare fin bool default false;
declare c cursor for select column_name from information_schema.columns where table_schema=db_name and table_name=tb_name;
declare continue handler for not found set fin=true;
drop temporary table if exists result_tb;
create temporary table result_tb (`Columns` varchar(10),`Values` varchar(25));
open c;
lp:loop
fetch c into col_name;
if fin=true then
leave lp;
end if;
set @stmt=concat('select sum(',col_name,') into @sum from test ;'); 
prepare stmt from @stmt;
execute stmt;
deallocate prepare stmt;

set @val=concat('sum(',col_name,') = ',@sum);
insert result_tb values(col_name,@val);
end loop lp;
close c;
select * from result_tb;
end//
delimiter ;

最后我们调用这个过程来得到想要的输出:

call table_sum('testdb','test'); 

Answer 2

除非我真的需要，否则我会避免使用预备语句和动态sql。当我需要对一个增加的值进行泛化时，我会使用这样一个强大的工具，在一个大的列集上。

在共享列的特定情况下，您可以使用一种简单的方法，对三个列进行并和。

SELECT 'A'    AS `Columns`, 
SUM(A) AS `Values`
FROM tab 
UNION
SELECT 'B'    AS `Columns`,
SUM(B) AS `Values`
FROM tab 
UNION
SELECT 'C'    AS `Columns`,
SUM(C) AS `Values`
FROM tab 

点击这里查看演示。