mysql - 如何在mysql中创建数据透视表？

Question

我正在尝试创建一个枢轴

我的 table :

|policies|
  |id|  |client|  |policy_business_unit_id|  |cia_ensure_id|   |state|       
   1      MATT                  1                   1             0
   2      STEVE                 1                   1             0
   3      BILL                  1                   2             0
   4      LARRY                 1                   2             1
   5      MATT                  1                   2             1
   6      STEVE                 2                   2             2
   7      BILL                  2                   2             2
   8      LARRY                 2                   2             1
   9      MATT                  2                   1             1

|policy_business_units|
   |id|   |name|  |comercial_area_id|
     1     LIFE         1 
     2     ROB          1 
     3     CAR          1 

|comercial_areas|
   |id|   |name|
    1      MICROSOFT
    2      APPLE

|cia_ensures|
   |id|   |name|
    1      ORANGE
    2      BT
    3      ATT
    4      MOVISTAR
    5      SPRINT

我试图像数据透视表一样连续显示信息

 UNIT  V1  A1  N1   V2 A2  N2   V3  A3  N3   V4  A4  N4   V5  A5  N5
 LIFE  2   0   0    1   2  0    0   0   0     0   0   0   0    0   0
 ROB   0   1   0    0   1  2    0   0   0     0   0   0   0    0   0 

我尝试了此查询，但如果我有 50 个policy_business_units，就会出现问题。

这里是查询:http://sqlfiddle.com/#!2/89491/1

SELECT pb.name as unit,
  SUM(CASE WHEN p.state =0 AND ce.id=1 THEN 1 ELSE 0 END ) AS v1, 
  SUM(CASE WHEN p.state =1 AND ce.id=1 THEN 1 ELSE 0 END ) AS a1, 
  SUM(CASE WHEN p.state =2 AND ce.id=1 THEN 1 ELSE 0 END ) AS n1,
  SUM(CASE WHEN p.state =0 AND ce.id=2 THEN 1 ELSE 0 END ) AS v2, 
  SUM(CASE WHEN p.state =1 AND ce.id=2 THEN 1 ELSE 0 END ) AS a2, 
  SUM(CASE WHEN p.state =2 AND ce.id=2 THEN 1 ELSE 0 END ) AS n2,
  SUM(CASE WHEN p.state =0 AND ce.id=3 THEN 1 ELSE 0 END ) AS v3, 
  SUM(CASE WHEN p.state =1 AND ce.id=3 THEN 1 ELSE 0 END ) AS a3, 
  SUM(CASE WHEN p.state =2 AND ce.id=3 THEN 1 ELSE 0 END ) AS n3,
  SUM(CASE WHEN p.state =0 AND ce.id=4 THEN 1 ELSE 0 END ) AS v4, 
  SUM(CASE WHEN p.state =1 AND ce.id=4 THEN 1 ELSE 0 END ) AS a4, 
  SUM(CASE WHEN p.state =2 AND ce.id=4 THEN 1 ELSE 0 END ) AS n4,
  SUM(CASE WHEN p.state =0 AND ce.id=5 THEN 1 ELSE 0 END ) AS v5, 
  SUM(CASE WHEN p.state =1 AND ce.id=5 THEN 1 ELSE 0 END ) AS a5, 
  SUM(CASE WHEN p.state =2 AND ce.id=5 THEN 1 ELSE 0 END ) AS n5 
  FROM cia_ensures ce 
  LEFT JOIN policies p on ce.id = p.cia_ensure_id
  INNER JOIN policy_business_units pb ON pb.id = p.policy_business_unit_id
  INNER JOIN comercial_areas ca ON ca.id = pb.comercial_area_id
  AND ca.id=1  
  Group by p.policy_business_unit_id; 

一位 friend 给了我这个查询http://sqlfiddle.com/#!2/9adb8/17

set @@local.group_concat_max_len=10000;
select distinct @sql := concat('SELECT pb.name as unit,',
group_concat(concat(
'SUM(CASE WHEN p.state =0 AND ce.id=',id,' THEN 1 ELSE 0 END ) AS v',id,', 
 SUM(CASE WHEN p.state =1 AND ce.id=',id,' THEN 1 ELSE 0 END ) AS a',id,', 
 SUM(CASE WHEN p.state =2 AND ce.id=',id,' THEN 1 ELSE 0 END ) AS n',id)),
' 
FROM cia_ensures ce 
LEFT JOIN policies p on ce.id = p.cia_ensure_id
INNER JOIN policy_business_units pb ON pb.id = p.policy_business_unit_id
INNER JOIN comercial_areas ca ON ca.id = pb.comercial_area_id
AND ca.id=1  
Group by p.policy_business_unit_id;')
from cia_ensures
where id in(1,2,3,4,5,6,7,8,10);

prepare stmt from @sql;
execute stmt;

是否有其他方法可以在不使用准备 stmt 和执行 stmt 的情况下更改此设置？

请问有人可以帮助我吗？

我将感谢所有帮助。

Answer 1

我觉得这太粗糙了，但应该可行:

Working Example @ MySQL Fiddle

select 
    max( case row_num when 1 then v else null end ) as V1
  , max( case row_num when 1 then a else null end ) as A1
  , max( case row_num when 1 then n else null end ) as N1

  , max( case row_num when 2 then v else null end ) as V2
  , max( case row_num when 2 then a else null end ) as A2
  , max( case row_num when 2 then n else null end ) as N2

  , max( case row_num when 3 then v else null end ) as V3
  , max( case row_num when 3 then a else null end ) as A3
  , max( case row_num when 3 then n else null end ) as N3

  , max( case row_num when 4 then v else null end ) as V4
  , max( case row_num when 4 then a else null end ) as A4
  , max( case row_num when 4 then n else null end ) as N4

  , max( case row_num when 5 then v else null end ) as V5
  , max( case row_num when 5 then a else null end ) as A5
  , max( case row_num when 5 then n else null end ) as N5
from (
  SELECT 
      SUM(CASE WHEN p.state =0 THEN 1 ELSE 0 END ) AS v
    , SUM(CASE WHEN p.state =1 THEN 1 ELSE 0 END ) AS a 
    , SUM(CASE WHEN p.state =2 THEN 1 ELSE 0 END ) AS n 
    , @row_num := ( @row_num + 1 ) as row_num
  FROM 
    ( select @row_num := 0 ) row_nums
    , cia_ensures ce 
    LEFT JOIN policies p 
      ON ce.id = p.cia_ensure_id 
     AND p.policy_business_unit_id =1
  WHERE ce.id IN (1,2,3,4,5,6,7,8,9,11,13)
  GROUP BY ce.id
) pivot_results;

输出为:

+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| V1   | A1   | N1   | V2   | A2   | N2   | V3   | A3   | N3   | V4   | A4   | N4   | V5   | A5   | N5   |
+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
|    2 |    0 |    0 |    1 |    2 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |
+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+