从表输出矩阵视图 - Output a matrix view from table

Output a matrix view from table

本文关键字：视图输出 | 更新日期: 2023-09-27

我是新手，请帮助我，我在带有 4 列的表中有数据，如下所示：

+---------------------+
| id  ah  av  score   |
+---------------------+
| 1   A   A    1      |
| 2   A   B    2      |
| 3   B   A   0.5     |
| 4   B   B   0.14    |
+---------------------+

我想输出这样的查询结果

+-------------------+
| Type   A    B     |
+-------------------+
| A      1    2     |
| B      0.5  0.14  |
+-------------------+

SELECT ah type
     , MAX(CASE WHEN av = 'A' THEN score END) 'A'
     , MAX(CASE WHEN av = 'B' THEN score END) 'B' 
  FROM my_table 
 GROUP 
    BY ah;
+------+------+------+
| type | A    | B    |
+------+------+------+
| A    | 1.00 | 2.00 |
| B    | 0.50 | 0.14 |
+------+------+------+

您的问题需要一个数据透视表，因为您正在"透视"其中一列以成为一行。

首先创建一个包含您需要的所有数据的视图（几乎放弃了 ID）：

create view Scores_Simple as (
select
   ah as Type,
   case when av = "A" then Score end as A,
   case when av = "B" then Score end as B
from Scores
);

然后对表进行透视：

create view Scores_Simple_Pivot as (
select
   Type,
   sum(A) as A,
   sum(B) as B
from Scores_Simple
group by Type
);

在这里测试