Mysql/Mariadb查询在查询数据时动态地将行转换为列
问题描述:
我查看了Mysql query to dynamically convert rows to columns的答案,但由于我需要连接表以获取所有信息,所以我无法使其工作。你能帮我把'问题'作为列名吗?Mysql/Mariadb查询在查询数据时动态地将行转换为列
datestamp pay benefits ... career advancement
---------------------------------------------------------------------
2/16/2017 11:55 Somewhat Slightyly Slightly
2/16/2017 11:55 Agree Somewhat Very
的数据是这样的:
id datestamp survey_col value
-----------------------------------------------
1 2/16/2017 11:55 885457X234X1368SQ001 A3
1 2/16/2017 11:55 885457X234X1368SQ002 A4
1 2/16/2017 11:55 885457X234X1368SQ003 A4
1 2/16/2017 11:55 885457X234X1368SQ004 A3
1 2/16/2017 11:55 885457X234X1368SQ005 A2
1 2/16/2017 11:55 885457X234X1368SQ006 A3
1 2/16/2017 11:55 885457X234X1368SQ007 A4
1 2/16/2017 11:55 885457X234X1368SQ008 A3
1 2/16/2017 11:55 885457X234X1368SQ009 A4
1 2/16/2017 11:55 885457X234X1368SQ010 A1
我使用这个代码:
SELECT T.id, T.datestamp, SQ.question,
IF(type IN ("K", "N", "S", "T", "Y", "*") AND type NOT IN ("F"),
T.value,
IF(parent_qid = 0, SA.answer, SA2.answer)
) as answer
FROM survey_questions SQ
JOIN survey_lookup SL ON SL.qid = SQ.qid
JOIN tmp T ON T.survey_col = SL.survey_col
LEFT JOIN survey_answers SA ON SA.qid = SQ.qid
AND SA.code = T.value
AND SA.language = 'en'
LEFT JOIN survey_answers SA2 on SA2.qid = SQ.parent_qid
AND SA2.code = T.value
AND SA2.language = 'en'
WHERE SQ.language = 'en'
;
其中规定:
id datestamp question answer
----------------------------------------------------------------
1 2/16/2017 11:55 Pay Somewhat
1 2/16/2017 11:55 Benefits (health plan, leave, etc.) Slightly
1 2/16/2017 11:55 Career Advancement Slightly
1 2/16/2017 11:55 Access to Training Somewhat
1 2/16/2017 11:55 Leadership Style Very
1 2/16/2017 11:55 Manager/Supervisor Style Somewhat
1 2/16/2017 11:55 Please enter comments The company's policies...
此代码工作时,所有的数据在一张桌子上,我不需要你se加入。
SET @sql = NULL;
SET SESSION GROUP_CONCAT_MAX_LEN = 1000000; -- default is 1024
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(question = ''', REPLACE(question,"'", "\\'"), ''', answer, NULL)) AS ''', REPLACE(question,"'", "\\'"), ''''
)
) INTO @sql
FROM tmp;
SET @sql = CONCAT('SELECT row_id, submitdate, ', @sql, ' FROM ', survey_report, ' GROUP BY row_id');
IF DEBUG = 1 THEN
SELECT @sql;
END IF;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
这是一个没有REPLACE功能的尝试(我想使用它)。
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(SQ.question = ', '''SQ.question''','
IF(type IN ("K", "N", "S", "T", "Y", "*") AND type NOT IN ("F"),
T.value,
IF(parent_qid = 0, SA.answer, SA2.answer)
) as answer, NULL)) as answer', '''SQ.question''')
) INTO @sql
FROM survey_questions SQ
JOIN survey_lookup SL ON SL.qid = SQ.qid
JOIN tmp T ON T.survey_col = SL.survey_col
LEFT JOIN survey_answers SA ON SA.qid = SQ.qid
AND SA.code = T.value
AND SA.language = 'en'
LEFT JOIN survey_answers SA2 on SA2.qid = SQ.parent_qid
AND SA2.code = T.value
AND SA2.language = 'en'
WHERE SQ.language = 'en';
SET @sql = CONCAT('SELECT id, datestamp, ', @sql, ' FROM tmp GROUP BY id');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
答
这工作,但我不想创建另一个表。
DROP TEMPORARY TABLE IF EXISTS `tmp2`;
CREATE TEMPORARY TABLE tmp2
SELECT T.id, T.datestamp, SQ.question,
IF(type IN ("K", "N", "S", "T", "Y", "*") AND type NOT IN ("F"),
T.value,
IF(parent_qid = 0, SA.answer, SA2.answer)
) as answer
FROM survey_questions SQ
JOIN survey_lookup SL ON SL.qid = SQ.qid
JOIN tmp T ON T.survey_col = SL.survey_col
LEFT JOIN survey_answers SA ON SA.qid = SQ.qid
AND SA.code = T.value
AND SA.language = 'en'
LEFT JOIN survey_answers SA2 on SA2.qid = SQ.parent_qid
AND SA2.code = T.value
AND SA2.language = 'en'
WHERE SQ.language = 'en'
;
- select * from tmp2;
SET @sql = NULL;
SET SESSION GROUP_CONCAT_MAX_LEN = 1000000; -- default is 1024
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(question = ''', REPLACE(question,"'", "\\'"), ''', answer, NULL)) AS ''', REPLACE(question,"'", "\\'"), ''''
)
) INTO @sql
FROM tmp2;
SET @sql = CONCAT('SELECT id, datestamp, ', @sql, ' FROM tmp2 GROUP BY id');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
您可能已经知道这称为* pivoting *表。你已经发现它在MySQL中是多么的痛苦。 –
我同意。我正在使用非常具有挑战性的软件来获取所需的数据。我使用游标来获取非常缓慢的数据,但是我发现工会使它快得多。所以,我使用联盟来获取数据,以便我可以创建联接。现在,我正在试图将它应用于我可以在图形软件中使用的格式。我尝试了几件事情,这似乎是最好的,因为它运行了11秒,其他解决方案我花了4分钟。 –
这是SQL解决问题的方法之一。编写PHP/Java /任何代码来进行格式化。使用SQL来提供数据。 –