SELECt a.ID, a.user_ID, a.job_id, MAX(CASE WHEN c.question = 'Is it this?' THEN b.answer END) 'Is it this?', MAX(CASE WHEN c.question = 'Or this?' THEN b.answer END) 'Or this?', MAX(CASE WHEN c.question = 'Or that? ' THEN b.answer END) 'Or that? 'FROM Results a INNER JOIN Answers b ON a.id = b.fk_result_id INNER JOIN Question c ON b.fk_question_id = c.IDGROUP BY a.ID, a.user_ID, a.job_id
- SQLFiddle演示
如果您不清楚问题的数量（ 例如Matei Mihai所说的1000个 ），则非常需要动态版本。
SET @sql = NULL;SELECt GROUP_CONCAt(DISTINCT CONCAt( 'MAX(CASE WHEN c.question = ''', question, ''' then b.answer end) AS ', CONCAt('`',question,'`') ) ) INTO @sqlFROM Question;SET @sql = CONCAt('SELECt a.ID, a.user_ID, a.job_id, ', @sql, ' FROM Results a INNER JOIN Answers b ON a.id = b.fk_result_id INNER JOIN Question c ON b.fk_question_id = c.ID GROUP BY a.ID, a.user_ID, a.job_id');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;- SQLFiddle演示
输出值
╔════╦═════════╦════════╦═════════════╦══════════╦══════════╗║ ID ║ USER_ID ║ JOB_ID ║ IS IT THIS? ║ OR THIS? ║ OR THAT? ║╠════╬═════════╬════════╬═════════════╬══════════╬══════════╣║ 1 ║ 1 ║ 1 ║ Yes ║ No ║ Maybe ║║ 2 ║ 1 ║ 3 ║ Maybe ║ No ║ Maybe ║║ 3 ║ 2 ║ 3 ║ Yes ║ Yes ║ No ║╚════╩═════════╩════════╩═════════════╩══════════╩══════════╝
