交叉表 mysql,在MySQL中使用交叉表查看？

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

I have two tables.

Clients

clID (primary key)

ClName (varchar)

Scores

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.

Thanks in advance.

解决方案

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.

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;

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;

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