if mysql sum 视图_通过将行转换为动态列数来在MySQL中创建汇总视图

I have a table in MySQL with the following fields:

id, company_name, year, state

There are multiple rows for the same customer and year, here is an example of the data:

id | company_name | year | state

----------------------------------------

1 | companyA | 2008 | 1

2 | companyB | 2009 | 2

3 | companyC | 2010 | 3

4 | companyB | 2009 | 1

5 | companyC | NULL | 3

I am trying to create a view from this table to show one company per row (i.e. GROUP BY pubco_name) where the state is the highest for a given year.

Here is an example of the view I am trying to create:

id | cuompany_name | NULL | 2008 | 2009 | 2010

--------------------------------------------------

1 | companyA | NULL | 1 | NULL | NULL

2 | companyB | NULL | 2 | NULL | NULL

3 | companyC | 3 | NULL | NULL | 3

There is a lot more data than this, but you can see what I am trying to accomplish.

I don't know how to select the max state for each year and group by pubco_name.

Here is the SQL I have thus far (I think we need to use CASE and/or sub-selects here):

SELECT

id,

company_name,

SUM(CASE WHEN year = 2008 THEN max(state) ELSE 0 END) AS 2008,

SUM(CASE WHEN year = 2009 THEN max(state) ELSE 0 END) AS 2009,

SUM(CASE WHEN year = 2010 THEN max(state) ELSE 0 END) AS 2010,

SUM(CASE WHEN year = 2011 THEN max(state) ELSE 0 END) AS 2011,

SUM(CASE WHEN year = 2012 THEN max(state) ELSE 0 END) AS 2012,

SUM(CASE WHEN year = 2013 THEN max(state) ELSE 0 END) AS 2013

FROM tbl

GROUP BY company_name

ORDER BY id DESC

Appreciate your help and thanks in advance.

解决方案

You need to pivot the table but mysql does not have any such functionality of pivot

so we need to replicate its functionality

EDITED

Select

group_concat(

DISTINCT

if(year is null,

CONCAT('max(if (year is null, state, 0)) as ''NULL'' '),

CONCAT('max(if (year=''', year, ''', state, 0)) as ''',year, ''' '))

) into @sql from tbl join (SELECT @sql:='')a;

set @sql = concat('select company_name, ', @sql, 'from tbl group by company_name;');

PREPARE stmt FROM @sql;

EXECUTE stmt;

Result

| COMPANY_NAME | 2008 | 2009 | 2010 | NULL |

--------------------------------------------

| companyA | 1 | 0 | 0 | 0 |

| companyB | 0 | 2 | 0 | 0 |

| companyC | 0 | 0 | 3 | 3 |

There are 2 approaches to solve your problem

1. create case for each year, which is not possible in your case as we are dealing with year

2. generate the query dynamically so that we get proper columns as per your need.

I have given solution according to the second solution where I am generating the query and storing it in @sql variable. In the fiddle I have printed the contents of @sql before executing it.

select company_name, max(if (year='2008', state, 0)) as '2008' ,max(if (year='2009', state, 0)) as '2009' ,max(if (year='2010', state, 0)) as '2010' ,max(if (year is null, state, 0)) as 'NULL' from tbl group by company_name;

For more information regarding group_concat() go through the link

Hope this helps..