SQL语句- SQL矩阵
SQL语句- SQL矩阵
提问于 2013-02-08 12:43:35
对于下表，是否可以创建SQL语句来创建数据矩阵或视图？
表：
TeamA|TeamB|Won|Lost
--------------------
A | B | 5 | 3
A | C | 2 | 4
A | D | 9 | 1
B | E | 5 | 5
C | A | 2 | 4Result-Matrix:
| A | B | C | D | E
----------------------------
A | 0 | 2 | -2 | 8 | 0
B | 0 | 0 | 0 | 0 | 0
C | -2 | 0 | 0 | 0 | 0回答 2
Stack Overflow用户
回答已采纳
发布于 2013-02-08 14:45:33
有两种方法可以使数据在MySQL中枢轴。如果您提前知道值(团队)，那么您将对这些值进行硬编码，或者可以使用准备好的语句生成动态sql。
静态版本如下：
select TeamA,
max(case when TeamB = 'A' then won - lost else 0 end) as A,
max(case when TeamB = 'B' then won - lost else 0 end) as B,
max(case when TeamB = 'C' then won - lost else 0 end) as C,
max(case when TeamB = 'D' then won - lost else 0 end) as D,
max(case when TeamB = 'E' then won - lost else 0 end) as E
from yourtable
group by TeamA;请参阅与Demo
如果要在准备好的语句中使用动态版本，代码如下：
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(CASE WHEN TeamB = ''',
TeamB,
''' THEN won - lost else 0 END) AS `',
TeamB, '`'
)
) INTO @sql
from
(
select *
from yourtable
order by teamb
) x;
SET @sql
= CONCAT('SELECT TeamA, ', @sql, '
from yourtable
group by TeamA');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;见与Demo。
编辑#1，在考虑到这一点之后，我实际上会做一些稍微不同的事情。我将生成一个真正的矩阵，即团队出现在行和列中的数据。要做到这一点，首先要使用UNION ALL查询将所有团队分成两列：
select teama Team1, teamb Team2,
won-lost Total
from yourtable
union all
select teamb, teama,
won-lost
from yourtable见与Demo。一旦完成，您就可以对数据进行枢轴操作：
select Team1,
coalesce(max(case when Team2 = 'A' then Total end), 0) as A,
coalesce(max(case when Team2 = 'B' then Total end), 0) as B,
coalesce(max(case when Team2 = 'C' then Total end), 0) as C,
coalesce(max(case when Team2 = 'D' then Total end), 0) as D,
coalesce(max(case when Team2 = 'E' then Total end), 0) as E
from
(
select teama Team1, teamb Team2,
won-lost Total
from yourtable
union all
select teamb, teama,
won-lost
from yourtable
) src
group by Team1;见与Demo。它给出了更详细的结果：
| TEAM1 | A | B | C | D | E |
-------------------------------
| A | 0 | 2 | -2 | 8 | 0 |
| B | 2 | 0 | 0 | 0 | 0 |
| C | -2 | 0 | 0 | 0 | 0 |
| D | 8 | 0 | 0 | 0 | 0 |
| E | 0 | 0 | 0 | 0 | 0 |Stack Overflow用户
发布于 2013-02-08 14:15:57
不能创建具有可变列数的SQL语句或视图。在标准SQL中，您可以通过执行以下操作来将数据枢轴：
select TeamA,
max(case when TeamB = 'A' then won - lost end) as A,
max(case when TeamB = 'B' then won - lost end) as B,
max(case when TeamB = 'C' then won - lost end) as C,
max(case when TeamB = 'D' then won - lost end) as D,
max(case when TeamB = 'E' then won - lost end) as E
from t
group by TeamA
order by 1一些数据库支持支点语句。
要做到这一点，您必须将SQL语句创建为字符串，然后执行它(通常称为动态SQL)。这种语句可以由SQL、存储过程、Excel或其他编程工具生成。然后就需要执行它。
让我重复一遍:任何给定的SQL语句都有一组预定义的列。不能更改列数。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14772882
复制相关文章
相似问题
腾讯云开发者
