MySQL“多维?"动态枢轴 [英] MySQL "Multi-Dimensional?" Dynamic Pivot

问题描述

我承认，我在头脑中.我已经走得很远，几乎可以看到隧道尽头的光线，但是，我不确定下一步应该采取什么措施.

I admit, I'm in over my head. I've gotten far enough that I can almost see light at the end of the tunnel, and yet, I'm not sure the next step to take.

我在这里创建了 SQLfiddle示例

这是SQL Pivot:

Here's the SQL Pivot:

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

我赞扬以下信息源，以帮助我走到这一步.

I credit the following sources for helping me get this far.

感谢@strapro的教程:

Thanks @strapro for his tutorial:

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

特别是@Rockse对Dynamic Pivots的回答，最终使我进入了@strapro的教程:

And specifically @Rockse's answer on Dynamic Pivots which ultimately led me to @strapro's tutorial:

MySQL数据透视表

推荐答案

您在错误的列上分组，请使用

You are grouping on the wrong column,use

GROUP BY r.rank_name

FIDDLE

这篇关于MySQL“多维?"动态枢轴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！