用几乎相同的子查询优化mySQL查询
用几乎相同的子查询优化mySQL查询
提问于 2014-11-07 10:57:01
是否有优化以下查询的方法？
SELECT s1.* FROM
(SELECT s.*
FROM answer a, answer_item ai, sessions s
WHERE s.session_state='FINISHED' AND a.sessionId=s.id AND ai.answer=a.id
AND a.question=1001 AND ai.question_variant_id=1103) s1,
(SELECT s.*
FROM answer a, answer_item ai, sessions s
WHERE s.session_state='FINISHED' AND a.sessionId=s.id AND ai.answer=a.id
AND a.question=1003 AND ai.question_variant_id=1301) s2
WHERE s1.id=s2.id 除了这些部分之外，一切似乎都是一样的：
a.question=1001 AND ai.question_variant_id=1103a.question=1003 AND ai.question_variant_id=1301
回答 4
Stack Overflow用户
回答已采纳
发布于 2014-11-07 11:58:36
这似乎是在寻找回答这两个问题的会话(或其他什么)。我想下面的内容可能会给你带来你想要的行。但是，为了获得正确的列，您可能需要做更多的工作：
SELECT s.*
FROM answer a JOIN
answer_item ai
ON ai.answer = a.id JOIN
sessions s
ON a.sessionId = s.id
WHERE s.session_state = 'FINISHED' AND
(a.question = 1001 AND ai.question_variant_id = 1103 OR
a.question = 1003 AND ai.question_variant_id = 1101
)
GROUP BY s.id
HAVING SUM(a.question = 1001 AND ai.question_variant_id = 1103) > 0 AND
SUM(a.question = 1003 AND ai.question_variant_id = 1101) > 0Stack Overflow用户
发布于 2014-11-07 10:59:16
SELECT s.*
FROM answer a
JOIN answer_item ai ON ai.answer=a.id
JOIN sessions s ON a.sessionId=s.id
WHERE s.session_state='FINISHED'
AND
(
a.question=1001 AND ai.question_variant_id=1103
OR a.question=1003 AND ai.question_variant_id=1301
)Stack Overflow用户
发布于 2014-11-07 11:03:44
检查下面的优化查询。
SELECT s.* FROM FROM answer a, answer_item ai, sessions s
WHERE s.session_state='FINISHED' AND a.sessionId=s.id AND ai.answer=a.id
AND (
(a.question=1001 AND ai.question_variant_id=1103)
OR
(a.question=1003 AND ai.question_variant_id=1301)
)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/26799700
复制相关文章
相似问题
腾讯云开发者
