MYSQL 旋转行并按特定列的行进行限制

Question

不完全确定如何表达这个问题，所以我尽了最大努力，希望解释能帮助理解这一点。有任何问题请追问。

我的数据库有以下设置:

create table spooner_pec
(
  id int,
  year varchar(20),
  policy_number int,
  primary_name varchar(200),
  experience_rate decimal(10,2),
  mco_name varchar(200)
);

插入这些值:

insert into spooner_pec values
(1,'2009',183586,'ZBIN LANDSCAPING INC', 1.22, 'GENEX CARE FOR OHIO'),
(1,'2011',183586,'ZBIN LANDSCAPING INC', 0.93, 'COMPMANAGEMENT HEALTH SYSTEMS, INC.'),
(1,'2012',183586,'ZBIN LANDSCAPING INC', 0.92, 'HEALTH MANAGEMENT SOLUTIONS, INC.'),
(1,'2013',183586,'ZBIN LANDSCAPING INC', 0.50, 'CAREWORKS'),
(1,'2014',183586,'ZBIN LANDSCAPING INC', 0.47, 'UNIVERSITY HOSPITALS COMPCARE'),
(1,'2010',183586,'ZBIN LANDSCAPING INC', 1.27, 'SHEAKLEY UNICOMP')

到目前为止我正在处理的查询是这样的:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when year = ',
      year,
      ' then experience_rate end) AS `',
      year, '-Pen`'
    ) ORDER BY year
  ) INTO @sql
FROM
  spooner_pec;
SET @sql = CONCAT('SELECT policy_number, primary_name, ', @sql, ' 
                  FROM spooner_pec 
                   GROUP BY policy_number');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

因此，我为每家公司设置一行，然后为每年设置一列年笔。例如，我现在需要做的只是获取最近一年的惩罚大于 1 的行。因此，如果所有数据输入的最后一年是 2014 年，我需要其中 2014-Pen 在本例中 > 1。

SQLFiddle

Answer 1

再执行一次查询以获取去年的数据，并将其合并到 SQL 中。

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when year = ',
      year,
      ' then experience_rate end) AS `',
      year, '-Pen`'
    ) ORDER BY year
  ) INTO @sql
FROM
  spooner_pec;
SELECT MAX(year) INTO @lastYear FROM spooner_pec;
SET @sql = CONCAT('SELECT policy_number, primary_name, ', @sql, ' 
                  FROM spooner_pec 
                   GROUP BY policy_number
                   HAVING `', @lastYear, '-Pen` > 1');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;