mysql - 通过将行旋转为动态列数在 MySQL 中创建摘要 View

Question

我在 MySQL 中有一个表，其中包含以下字段:

id, company_name, year, state

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

    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

我正在尝试从该表创建一个 View ，以每行显示一家公司(即 GROUP BY pubco_name)，其中该州在给定年份最高。

这是我尝试创建的 View 示例:

    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

数据比这多得多，但您可以看到我正在努力完成的任务。

我不知道如何选择每年的最大状态并按 pubco_name 分组。这是我到目前为止的 SQL(我认为我们需要在这里使用 CASE 和/或 sub-selects):

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

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

Answer 1

你需要对表进行透视，但是mysql没有这样的透视功能

所以我们需要复制它的功能

已编辑

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;

结果

| COMPANY_NAME | 2008 | 2009 | 2010 | NULL |
--------------------------------------------
|     companyA |    1 |    0 |    0 |    0 |
|     companyB |    0 |    2 |    0 |    0 |
|     companyC |    0 |    0 |    3 |    3 |

SQL FIDDLE

有两种方法可以解决您的问题1. 为每年创建案例，这在您的案例中是不可能的，因为我们正在处理年份2. 动态生成查询，以便我们根据您的需要获得适当的列。

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

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; 

有关 group_concat() 的更多信息，请访问链接<强> GROUP_CONCAT 和<强> USER DEFINED VARIABLE

希望这有帮助..