通过将行转换为动态列数来在MySQL中创建汇总视图 [英] Create a Summary View in MySQL by pivoting row into dynamic number of columns

问题描述

  id，company_name，year，state 
  

同一客户和年份有多行，这里是一个数据示例：

  id | company_name |年| |州
 ---------------------------------------- 
 1 | companyA | 2008 | 1 
 2 |公司B | 2009 | 2 
 3 | companyC | 2010 | 3 
 4 |公司B | 2009 | 1 
 5 | companyC | NULL | 3 
  

我试图从这个表创建一个视图来显示每行一个公司（即 GROUP BY pubco_name）其中州是给定年份中最高的州。

以下是我的视图示例试图创建：

  id | cuompany_name | NULL | 2008 | 2009 | 2010 
 --------------------------------------------- ----- 
 1 | companyA | NULL | 1 | NULL | NULL 
 2 |公司B | NULL | 2 | NULL | NULL 
 3 | companyC | 3 | NULL | NULL | 3 
  

比这更多的数据，但你可以看到我想要完成的事情。

我不知道如何选择每年的最大状态和按pubco_name分组。
这里是我到目前为止的SQL（我认为我们需要使用 CASE 和/或子选择

 选择
 id，
 company_name，
 SUM（CASE WHEN年份= 2008 THEN最大（状态）否则0结束）AS 2008，
 SUM（年份= 2009年THEN最大（州）否则0结束）2009年，
 SUM（年份= 2010年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 
  

感谢您的帮助，并提前致谢。

所以我们需要复制其功能

编辑

 选取
 GROUP_CONCAT（
 DISTINCT 
）如果（year为空，
 CONCAT（'max（if（year为null，state，0））为''NULL'''）， 
 CONCAT（'max（if（year ='''，year，''，state，0））as'''，year，''''））
） tbl join（SELECT @sql：=''）a; 
 set @sql = concat（'select company_name，'，@sql，'from tbl group by company_name;'）; 
 PREPARE stmt FROM @sql; 
 EXECUTE stmt; 
  

结果

  | COMPANY_NAME | 2008 | 2009 | 2010 | NULL | 
 -------------------------------------------- 
 | companyA | 1 | 0 | 0 | 0 | 
 |公司B | 0 | 2 | 0 | 0 | 
 | companyC | 0 | 0 | 3 | 3 | 
  

SQL FIDDLE

有两种方法可以解决您的问题
1. create case每年，这是不可能在你的情况下，因为我们正在处理年
2.动态生成查询，以便我们根据您的需要得到适当的列。

我根据第二种解决方案给出了解决方案，我在其中生成查询并将其存储在 @sql 变量中。在执行它之前，我已经打印了 @sql 的内容。

 <$ c $ （年份='2009'，州，0））作为'2009'，max（如果（year ='2009'，州，0）），选择company_name，max ='2010'，state，0））作为'2010'，max（如果（year为null，state，0））为tbl group by company_name的'NULL' 
  

有关 group_concat（）的更多信息，请执行go通过链接
GROUP_CONCAT 和
USER DEFINED VARIABLE

希望这有助于..

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 |

SQL FIDDLE

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 GROUP_CONCAT and USER DEFINED VARIABLE

Hope this helps..

这篇关于通过将行转换为动态列数来在MySQL中创建汇总视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！