在MySQL中使用交叉表查看? [英] Crosstab View in mySQL?

问题描述

我不知道我的标题是否正确，但让我解释一下我要寻找的内容.

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

得分表数据如下

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

如果客户端数量未知，或者您将要添加新的客户端而无需更改代码，则可以使用准备好的语句来生成动态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提琴.这两个查询将给出相同的结果.

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

这篇关于在MySQL中使用交叉表查看?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！