如何在SELECT语句中将查询结果用作列名 [英] How to use a query's results as column names in a SELECT statement
问题描述
我的最终目标是在MySQL中基于另一个表的内容创建带有动态列的数据透视表视图.目前,我正尝试从 artfulsoftware 离开的地方继续;现在,我可以查询可以给我想要的列名的结果.不幸的是,我对如何将结果实际用作SELECT语句中的列名一无所知.我怀疑MySQL变量会有所帮助,但我无法弄清楚.
My ultimate goal is to create a pivot-table view in MySQL with dynamic columns based on the contents of another table. At the moment I am trying to continue on from where artfulsoftware leaves off; right now I can query for results that give me my desired column names. Unfortunately I'm lost on how to actually use the results as column names in a SELECT statement. I suspect that MySQL variables will be helpful, but I can't figure it out.
要弄清问题,说我有一个像这样的表:
To clarify the problem, say I have a table like:
+---------------------------------------------------+
| countpivotarg |
+---------------------------------------------------+
| ,SUM(IF(domain = "test.com",1,0)) AS `test.com` |
| ,SUM(IF(domain = "test2.com",1,0)) AS `test2.com` |
+---------------------------------------------------+
我想创建一个如下所示的select语句:
I want to create a select statement that looks like:
SELECT id,
meta_id,
SUM(IF(domain = "test.com",1,0)) AS `test.com`,
SUM(IF(domain = "test2.com",1,0)) AS `test2.com`
FROM myTable;
我该怎么做?
推荐答案
You may use MySQL Server prepared statements for building dynamic queries from string variables.
示例:
SELECT domain INTO @colname FROM myTable LIMIT 1;
SET @s = CONCAT('SELECT `',@colname,'` FROM myTable');
PREPARE stmt FROM @s;
EXECUTE stmt;
注意:如果列名包含空格,则必须使用反引号.
Note: backticks are required in case column names contain spaces.
这篇关于如何在SELECT语句中将查询结果用作列名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!