SQL语句-SQL矩阵 [英] SQL Statement - SQL Matrix

问题描述

对于下表，是否可以创建SQL语句来创建数据矩阵或视图?

For the following Table, is it possible, to create an SQL Statement to create a data Matrix or view?

表格:

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.

There are two ways that you can pivot data in MySQL. If you know the values ahead of time (teams) then you will hard-code the values or you can use a prepared statement to generate dynamic 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;

请参见带有演示的SQL小提琴

如果要使用带有预准备语句的动态版本，则代码为:

If you want to use a dynamic version with a prepared statement, the code would be:

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提琴.

编辑#1，在考虑了这一点之后，我实际上会做一点点不同.我将在团队出现在行和列中的数据上生成一个真正的矩阵.为此，您首先需要使用UNION ALL查询将所有团队分成两列:

Edit #1, after thinking about this I would actually do this a slight bit different. I would generate a true matrix os the data where the teams appeared in both the row and the column. To do this you would first use a UNION ALL query to get all teams in two columns:

select teama Team1, teamb Team2,
  won-lost Total
from yourtable
union all
select teamb, teama,
  won-lost
from yourtable

请参见带演示的SQL小提琴.完成后，您将透视数据:

See SQL Fiddle with Demo. Once that is done, then you would pivot the data:

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小提琴.给出更详细的结果:

See SQL Fiddle with Demo. Which gives a more detailed result of:

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

这篇关于SQL语句-SQL矩阵的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！