动态数据透视表表mysql [英] Dynamic Pivot table columns mysql
问题描述
我有下表
人口统计类别
demographic_id demographic_name
1 color
2 age_group
project_tests
test_id group_id project_id
1 1 1
2 1 1
test_demographic_requirements
test_id project_id demgraphic_id demographic_value
1 1 1 blue
1 1 2 young
2 1 1 green
2 1 2 middle
我需要一个查询,该查询将给我以下结果:
And I need a query which would give me following result :
test_id group_id color age_group
1 1 blue young
2 1 green middle
我想我们需要使用数据透视表的概念来获取结果,但是我做不到.而且我的受众特征类别是否可以趋于相同,所以它们可能会发生变化,所以我需要动态的内容,那么什么是最好的方法?
I guess we need to use concept of pivot table to get the result, but I am unable to. And I demographic categories can we might be same they tend to change so I need something dynamic so what would be the best way to do it ?
我已经尝试根据以前的类似问题进行以下操作,但它似乎对我不起作用,这是我尝试过的操作:
I have tried doing the following based of previous similar questions but it doesn't seems to work for me, here is what I have tried:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when dc.demographic_name = ''',
demographic_name,
''' then trd.demographic_value end) AS ',
replace(demographic_name, ' ', '')
)
) INTO @sql
from demographic_categories;
SET @sql = CONCAT('SELECT pt.test_id, pt.group_id,
', @sql,'
from test_requirement_demographic trd
LEFT JOIN demographic_categories dc ON trd.demographic_id = dc.demographic_id
LEFT JOIN project_tests pt ON pt.test_id = trd.test_id and project_id =1
group by pt.test_id;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
推荐答案
您的动态SQL可以很好地运行,除了一件事.在您的第二个左连接处,它的列project_id
含糊不清,只需将其替换为pt.project_id
或trd.project_id
,它将得到所需的结果.
Your dynamic SQL is just fine except for one thing. It has an ambiguous column project_id
on your second left join, Just replace it with pt.project_id
or trd.project_id
and it will give desired result.
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when dc.demographic_name = ''',
demographic_name,
''' then trd.demographic_value end) AS ',
replace(demographic_name, ' ', '')
)
) INTO @sql
from demographic_categories;
SET @sql = CONCAT('SELECT pt.test_id, pt.group_id,
', @sql,'
from test_demographic_requirements trd
LEFT JOIN demographic_categories dc ON trd.demographic_id = dc.demographic_id
LEFT JOIN project_tests pt ON pt.test_id = trd.test_id and pt.project_id =1
group by pt.test_id;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
我跑了一个月.这是链接: dynamic_pivot_test
I ran it on a rextester. Here is the link : dynamic_pivot_test
这篇关于动态数据透视表表mysql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!