如何在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屋！