MySQL中的动态Crosstab查询 [英] Dynamic Crosstab Query in MySQL
本文介绍了MySQL中的动态Crosstab查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
使用Crosstab Query从SQL Server切换到MySQL时出现问题。
I have a problem when switching from SQL Server to MySQL with Crosstab Query.
假设我有一个这样的表:
Suppose that I have a table like this:
| 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中创建此类交叉表?
How can I create this kind of crosstab in MySQL?
您可以在此处测试数据: a href =http://sqlfiddle.com/#!9/9cf88/1 =nofollow> http://sqlfiddle.com/#!9/9cf88/1
You can test data at here : http://sqlfiddle.com/#!9/9cf88/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;
这篇关于MySQL中的动态Crosstab查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文