mySQL 中的交叉表视图?

2021-12-29 00:00:00 sql mysql view

我不知道我的标题是否正确，但让我解释一下我在寻找什么.

I dont know I titled this correct, but let me explain what i am looking for.

我有两张桌子.

clID   (primary key)
ClName (varchar)

分数

ID     (Primay key)
clID   (F Key)
PlayDate  (Date/Time)
Score     (double)

<小时>

客户表数据看起来像这样

Client table data looks like this

clID  clName
1     Chris
2     Gale
3     Donna

分数表数据看起来像这样

Scores table data looks like this

ID  clID  PlayDate    Score
1   2     23/01/2012  -0.0125
2   2     24/01/2012  0.1011
3   3     24/01/2012  0.0002
4   3     26/01/2012  -0.0056
5   3     27/01/2012  0.0001
6   1     12/01/2012  0.0122
7   1     13/01/2012  0.0053

<小时>

是否可以创建一个看起来像这样的视图

Is it possible to create a view that will look like this

Date         Chris   Gale    Donna
12/01/2012   0.0122   -        -
13/01/2012   0.0053   -        -
23/01/2012     -     -0.0125   -
24/01/2012     -     0.1011  0.0002
26/01/2012     -        -    -0.0056
27/01/2012     -        -    0.0001

如果稍后有另一个新客户，那么我应该能够在现在将在此视图中创建的新列中检查该新客户的数据.

If later there is a another new client then i should be able to check the data for that new client in the new column that will be now created in this view.

提前致谢.

推荐答案

这种类型的数据转换称为 PIVOT.MySQL 没有数据透视函数，但您可以使用带有 CASE 表达式的聚合函数来获取结果.

This type of data transformation is called a PIVOT. MySQL does not have a pivot function but you can use an aggregate function with a CASE expression to get the result.

如果提前知道 clients 的名称，那么您可以对查询进行硬编码:

If the names of the clients is known ahead of time, then you can hard-code the query:

select s.playdate,
  sum(case when clname = 'Chris' then score end) Chris,
  sum(case when clname = 'Gale' then score end) Gale,
  sum(case when clname = 'Donna' then score end) Donna
from clients c
inner join scores s
  on c.clid = s.clid
group by s.playdate;

请参阅SQL Fiddle with Demo.

如果您的客户端数量未知，或者您将添加您希望包含的新客户端而无需更改代码，那么您可以使用准备好的语句来生成动态 SQL:

If you have an unknown number of clients or you will be adding new clients that you will want included without having to change the code, then you can use a prepared statement to generate dynamic SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN clName = ''',
      clName,
      ''' THEN score else ''-'' END) AS `',
      clName, '`'
    )
  ) INTO @sql
FROM clients;

SET @sql 
  = CONCAT('SELECT s.playdate, ', @sql, ' 
            from clients c
            inner join scores s
              on c.clid = s.clid
            group by s.playdate');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

参见SQL Fiddle with Demo.两个查询都会给出相同的结果.

See SQL Fiddle with Demo. Both queries will give the same result.

相关文章