具有动态列名的Mysql查询 [英] Mysql query with dynamic columns names

问题描述

我有一个mysql查询，它给了我整个结果。

I have a mysql query that gives me the entire results.

SELECT `respondents`.`session_id`, `answers`.`respondent_id`, `answers`.`question_id`, `respondents`.`center_id`, `respondents`.`survey_id`, `respondents`.`end_date`, `answervalues`.`spss_name`, `answervalues`.`answer_id`, `answervalues`.`value`, `answervalues`.`district_code`, `answervalues`.`county_code`, `answervalues`.`option`, `answervalues`.`is_answer`, (
SELECT COUNT(*)
FROM answervalues
WHERE answer_id = answers.id AND is_answer = 1) AS has_answer
FROM `answervalues`
INNER JOIN `answers` ON `answers`.`id` = `answervalues`.`answer_id`
INNER JOIN `respondents` ON `respondents`.`id` = `answers`.`respondent_id`
WHERE `respondents`.`center_id` IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '28', '29', '30', '32') AND `respondents`.`survey_id` IN ('2', '3', '4', '5', '6', '7', '8') AND `answers`.`question_id` IN ('8', '24', '58', '74', '83', '94', '106', '9', '25', '59', '75', '95', '107', '155', '10', '26', '60', '96', '108', '154', '156', '11', '27', '61', '76', '84', '97', '109', '14', '28', '62', '77') AND `respondents`.`end_date` IS NOT NULL AND `respondents`.`end_date` >= '2016-01-01 00:00:00' AND `respondents`.`end_date` <= '2016-05-31 23:59:59'
ORDER BY `respondents`.`session_id` ASC, `respondents`.`survey_id` ASC, `answers`.`question_number` ASC, `answervalues`.`spss_name` ASC

我想要的是执行一个查询，其中结果按respondents.session_id分组，并且通过字段[answervalues]的结果动态创建列。[spss_name]和行的值是[answervalues]。[value]或者如果它的值为null，则值应来自[answervalues]。[option]。不需要计算值。



谢谢大家。



我的尝试： < br $>


使用查询结果构建一个新表并导出到csv文件。但是因为数据太大而无法在excel中工作。

What i want is to perform a query where the results are grouped by the "respondents.session_id" and the columns created dynamic by the result of the field [answervalues].[spss_name] and the values for the rows are the [answervalues].[value] or if its null the values should be from [answervalues].[option]. No calculations for the values are needed.

Thanks to you all.

What I have tried:

Build a new table with the query results and export to csv file. But since the data is too big its impossivle to work in excel.

推荐答案

将原始查询用作嵌套子查询（即将其括在两个括号之间）与任何别名，然后按所需字段分组。它应该是这样的：

Use your original query as a nested subquery (i.e, enclose it between two brackets) with any alias and then group by your desired field. It should be like this:

SELECT * FROM (YOUR_FULL_QUERY_SHOUD_BE_HERE) AS t
GROUP BY t.session_id

条件答案值你必须选择这样的CASE条件：

for the conditional answer value you have to select a CASE condition like this:

CASE WHEN has_answer > 0 THEN `spss_name` ELSE
`option` END AS spss_name_option

*您必须注意未分组的字段，因为在MySQL中，如果您尝试选择没有任何聚合功能的非分组字段，您可能会丢失信息（它只会返回如果您未指定所需的聚合值，例如MAX，MIN，SUM，COUNT，等等，则该字段的任何单个值。你可以在MySQL中使用一个非常有用的函数：GROUP_CONCAT和DISTINCT选项用于你可能需要它所有多个数据的字段



例如：

* You have to take care about non-grouped fields, because in MySQL you can lose information if you try to select non-grouped fields without any aggregation function (it will simply return any single value of the field if you don't specify what is your desired aggregated value such as MAX, MIN, SUM, COUNT, ect...). You can use a very useful function in MySQL: GROUP_CONCAT with DISTINCT option for fields you might need all of its multiple data

for instance:

CAST(GROUP_CONCAT(/*DISTINCT*/`question_id`) AS CHAR) AS `question_id`

这篇关于具有动态列名的Mysql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！