select语句查询日期mysql查询结果带列名_如何在SELECT语句中将查询结果⽤作
列名
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",1,0)) AS `test` |
| ,SUM(IF(domain = "test2",1,0)) AS `test2` |
+---------------------------------------------------+
I want to create a select statement that looks like:
SELECT id,
meta_id,
SUM(IF(domain = "test",1,0)) AS `test`,
SUM(IF(domain = "test2",1,0)) AS `test2`
FROM myTable;
How do I go about doing that?
解决⽅案
You may use MySQL Server prepared statements for building dynamic queries from string variables.
Example:
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.