mysql - MySQL 中的动态交叉表查询

Question

我在使用交叉表查询从 SQL Server 切换到 MySQL 时遇到问题。

假设我有这样一张表:

| ID | BANKID | 1MONTH |        3MONTHS |        6MONTHS |       10MONTHS |       12MONTHS | 18MONTHS |       24MONTHS |       30MONTHS |       36MONTHS |
|----|--------|--------|----------------|----------------|----------------|----------------|----------|----------------|----------------|----------------|
|  1 |      1 |      3 | 2.900000095367 | 2.799999952316 | 2.700000047684 | 2.599999904633 |      2.5 | 2.400000095367 | 2.299999952316 | 2.200000047684 |
|  2 |      2 |      5 | 4.900000095367 | 4.800000190735 | 4.699999809265 | 4.599999904633 |      4.5 | 4.400000095367 | 4.300000190735 | 4.199999809265 |

我想这样显示:

BankID           1          2
1 Month          3          5
3 Months         2.9        4.9
6 Months         2.8        4.8
10 Months        2.7        4.7
12 Months        2.6        4.6
18 Months        2.5        4.5
24 Months        2.4        4.4
30 Months        2.3        4.3
36 Months        2.2        4.2

如何在 MySQL 中创建这种交叉表？

您可以在此处测试数据:http://sqlfiddle.com/#!9/9cf88/1

谢谢!

Answer 1

使用以下查询

Set @Sq = NUll;
Set @S =Null;
SET @sql = NULL;

SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN id = ',
      BankID,' THEN val end) AS "',
      BankID,'"')

    )
into @Sql
FROM
  bankdeposit;


SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'select id, ''',
      c.column_name,
      ''' as Bankid, ',
      c.column_name,
      ' as val 
      from bankdeposit'
    ) SEPARATOR ' UNION ALL '
  ) into @xSq
FROM information_schema.columns c
where c.table_name = 'bankdeposit'
  and c.column_name not in ('id','BankID',
 'CreateDate', 'CreateBy', 'ModifyDate', 'ModifyBy',
 'totalLoan','TotalDeposit','EstablishedYear','NumberOfStore')
order by c.ordinal_position;


SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'select id, ''',
      c.column_name,
      ''' as Bankid, ',
      c.column_name,
      ' as val 
      from bankdeposit'
    ) SEPARATOR ' UNION ALL '
  ) into @S
FROM information_schema.columns c
where c.table_name = 'bankdeposit'
  and c.column_name in ('totalLoan','TotalDeposit',
  'EstablishedYear','NumberOfStore' )
order by c.ordinal_position;


select CONCAT('select Bankid,',@sql,' from(select id, Bankid, val
           from
           (', @xSq,',',@S,') x  order by id) xx group by Bankid 
           order by length(BankID),BankID');

PREPARE stmt FROM @xSq;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Sql Fiddle Here