SQL语句

您可以通过两种方式在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 Efrom yourtablegroup by TeamA;

参见带有演示的SQL Fiddle

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

SET @sql = NULL;SELECt  GROUP_CONCAt(DISTINCT    CONCAt(      'MAX(CASE WHEN TeamB = ''',      TeamB,      ''' THEN won - lost else 0 END) AS `',      TeamB, '`'    )  ) INTO @sqlfrom(  select *  from yourtable  order by teamb) x;SET @sql   = CONCAt('SELECt TeamA, ', @sql, ' from yourtablegroup by TeamA');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;

请参阅带有演示的SQL Fiddle。

编辑＃1，在考虑了这一点之后，我实际上会做一点点不同。我将在团队出现在行和列中的数据上生成一个真正的矩阵。为此，您首先要使用

UNIOnALL

select teama Team1, teamb Team2,  won-lost Totalfrom yourtableunion allselect teamb, teama,  won-lostfrom 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 Efrom(  select teama Team1, teamb Team2,    won-lost Total  from yourtable  union all  select teamb, teama,    won-lost  from yourtable) srcgroup 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 |