SQL语句-SQL矩阵
对于下表,是否可以创建SQL语句来创建数据矩阵或视图?
表:
TeamA|TeamB|Won|Lost
--------------------
A | B | 5 | 3
A | C | 2 | 4
A | D | 9 | 1
B | E | 5 | 5
C | A | 2 | 4
结果矩阵:
| A | B | C | D | E
----------------------------
A | 0 | 2 | -2 | 8 | 0
B | 0 | 0 | 0 | 0 | 0
C | -2 | 0 | 0 | 0 | 0
-
您可以通过两种方式在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;
如果要使用带有预准备语句的动态版本,则代码为:
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;
请参阅带有演示的SQL Fiddle。
编辑#1,在考虑了这一点之后,我实际上会做一点点不同。我将在团队出现在行和列中的数据上生成一个真正的矩阵。为此,您首先要使用
UNION ALL
查询在两列中获取所有团队:select teama Team1, teamb Team2, won-lost Total from yourtable union all select teamb, teama, won-lost from yourtable
请参阅带有演示的SQL Fiddle。一旦完成,就可以 旋转 数据:
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;
请参阅带有演示的SQL Fiddle。这给出了更详细的结果:
| 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 |