MySQL "Multi-Dimensional?"动态数据透视表

Question

我承认，我脑子有问题。我已经走得够远了，几乎可以看到隧道尽头的曙光，但我不确定要采取的下一步。

我创建了一个 SQLfiddle example here

这是 SQL 数据透视表:

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(qrv.req_name = ''',qrv.req_name,''', qrv.req_value, NULL)) AS `',qrv.req_name,'`')) INTO @sql
FROM (SELECT qrt.req_name, qrv.id, qrv.req_value FROM qual_requirment_values qrv JOIN qual_requirment_types qrt ON qrt.id = qrv.req_type_id) qrv;

SET @sql = CONCAT('SELECT r.id, r.rank_name, 
                     ', @sql, ' 
                   FROM qual_rank_requirments qrr
                   LEFT JOIN (
                              SELECT qrt.req_name, qrv.id, qrv.req_value 
                                 FROM qual_requirment_values qrv 
                                JOIN qual_requirment_types qrt ON qrt.id = qrv.req_type_id
                             ) AS qrv  ON qrv.id = qrr.req_values_id                   
                   JOIN ranks r ON r.id = qrr.rank_id
                   GROUP BY qrv.id');

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

这是数据结构:

create table qual_rank_requirments
(
  id int,
  rank_id int,
  req_values_id int
);

insert into qual_rank_requirments values
(1, 4, 1),
(2, 4, 2),
(3, 5, 3),
(4, 5, 4),
(5, 6, 3),
(6, 6, 5),
(7, 7, 3),
(8, 7, 6),
(9, 8, 3),
(10, 8, 7);

create table qual_requirment_values
(
  id int,
  req_type_id int,
  req_value int
);

insert into qual_requirment_values values
(1, 1, 55),
(2, 3, 1100),
(3, 1, 110),
(4, 4, 2530),
(5, 5, 4950),
(6, 6, 14630),
(7, 6, 19800);

create table qual_requirment_types
(
  id int,
  req_name varchar(50)
);

insert into qual_requirment_types values
(1, 'pv'),
(2, 'psv'),
(3, 'tv4'),
(4, 'tv5'),
(5, 'tv6'),
(6, 'tv7');

create table ranks
(
  id int,
  rank_name varchar(50)
);

insert into ranks values
(4, 'gyv1'),
(5, 'gyv2'),
(6, 'gyv3'),
(7, 'gyv4'),
(8, 'yns1');

这是我得到的:

id  rank_name   pv      tv4     tv5     tv6     tv7
4   gyv1        55      (null)  (null)  (null)  (null)
4   gyv1        (null)  1100    (null)  (null)  (null)
5   gyv2        110     (null)  (null)  (null)  (null)
5   gyv2        (null)  (null)  2530    (null)  (null)
6   gyv3        (null)  (null)  (null)  4950    (null)
7   gyv4        (null)  (null)  (null)  (null)  14630
8   yns1        (null)  (null)  (null)  (null)  19800

这是我的目标:

id  rank_name   pv      tv4     tv5     tv6     tv7
4   gyv1        55      1100    (null)  (null)  (null)
5   gyv2        110     (null)  2530    (null)  (null)
6   gyv3        110     (null)  (null)  4950    (null)
7   gyv4        110     (null)  (null)  (null)  14630
8   yns1        110     (null)  (null)  (null)  19800

我感谢以下资源帮助我走到这一步。

感谢@strapro 的教程:

http://stratosprovatopoulos.com/web-development/mysql/pivot-table-with-dynamic-columns/

特别是 @Rockse 关于 Dynamic Pivots 的回答，这最终让我看到了@strapro 的教程:

MySQL pivot table

Answer 1

你在错误的列上分组，使用

GROUP BY r.rank_name

FIDDLE