mysql - 这个复杂的 mysql 查询的更好选择

Question

我有两个表

1.fw_respondent_answers
2.fw_question_options

结构是:

fw_respondent_answers

id invitationid qdetailid optionid
1     2          1          1
2     2          2          2
3     2          3          3
4     3          1          4
5     3          2          5
6     3          3          6

fw_question_options:

id optionname qdetailid
1   india        1
2   teacher      2
3   ok           3
4   france       1
5   assistane    2
6   good         3

期望的输出:

invitationid     country profession answer
    2            india     teacher   ok
    3            france    assistant good

说明:我想获取与特定 invitationid 关联的 optionname 对应于 qdetailid 和 选项ID。optionid是fw_question_options(id)的主键。

我尝试过的:

SELECT distinct fra.`invitationid` ,fo.optionname as country,
fo1.optionname as profession,fo2.optionname as nps
FROM `fw_respondent_answers` fra,fw_question_options fo,
`fw_respondent_answers` fra1,fw_question_options fo1,
`fw_respondent_answers` fra2,fw_question_options fo2
WHERE fra.`optionid`=fo.id and fra.`qdetailid`=2 
and  fra1.`optionid`=fo1.id and fra1.`qdetailid`=1
and  fra2.`optionid`=fo2.id and fra2.`qdetailid`=3

问题:我上面的查询很慢。我只想知道除了上面的查询还有其他更好的选择吗？

提前致谢。

Answer 1

试试这个:

SELECT a.invitationid, 
         MAX(IF(a.qdetailid = 1, b.optionname, '')) AS country, 
         MAX(IF(a.qdetailid = 2, b.optionname, '')) AS profession, 
         MAX(IF(a.qdetailid = 3, b.optionname, '')) AS nps
FROM fw_respondent_answers a 
INNER JOIN fw_question_options b ON a.optionid = b.id 
GROUP BY a.invitationid