mysql三个条件怎么写_如何写多个问题回答条件的mysql 查询语句

最新推荐文章于 2024-06-30 07:27:16 发布

春华秋实的夏

最新推荐文章于 2024-06-30 07:27:16 发布

阅读量1k

点赞数

文章标签： mysql三个条件怎么写

本文链接：https://blog.csdn.net/weixin_34190683/article/details/113558070

版权

在处理大规模市场调查数据时，需要找出满足特定条件组合的回答，例如问题Q1、Q2等多个问题的答案为1、8或9。当前的MySQL查询语句虽然能实现这一功能，但在运行效率上存在不足，耗时12秒。寻求优化方法以提高查询速度。

摘要由CSDN通过智能技术生成

开发市场调查业务，有一个调查问题表responses (一百万行), 是市场调查的原始数据，包含survey_id(调查表的类型列)，response_no(被调查者列)，interview_date(调查日期)， question_label(问题列), value(回答列), section_unique_id(部门列)等。

每一行对应某个被调查者回答的一个问题和它的调查结果。一个被调查者一次会回答30个问题，所以会产生30行。

另有一个计算公式表 (40行), 是对调查结果的分析计算公式，这些公式都是sql语句。

根据计算公式，产生一个结果表results。

现在的问题是,要设计一些sql语句，调查有多少人的回答是类似如下这样的组合：

1. 回答问题Q1,答案是1或8或9

并且

2. 回答问题Q2,答案是1或8或9

并且

。。。。。。

最好能用group by section。

比如如下这个例子：

计算公式的说明是这样。

((Q2A = 1 OR Q2A = 8 OR Q2A = 9) AND (Q2B = 1 OR Q2B = 8 OR Q2B = 9) AND (Q2C = 1 OR Q2C = 8 OR Q2C = 9) AND (Q2D = 1 OR Q2D = 8 OR Q2D = 9) AND (Q2E = 1 OR Q2E = 8 OR Q2E = 9) AND (Q2F = 1 OR Q2F = 8 OR Q2F = 9) AND (Q2G = 1 OR Q2G = 8 OR Q2G = 9) AND (Q2H = 1 OR Q2H = 8 OR Q2H = 9) AND (Q2I = 1 OR Q2I = 8 OR Q2I = 9) AND (Q5 = 1 OR Q5 = 8 OR Q5 = 9) AND (Q6 = 1 OR Q6 = 8 OR Q6 = 9))

我现在写了这样一个mysql语句：

SELECT section_unique_id as "section_unique_id", COUNT(*) as "hit" FROM responses WHERE

question_label = "Q2A" AND value IN (1,8,9)

AND

(response_no, survey_id, interview_date) IN (SELECT DISTINCT response_no, survey_id, interview_date FROM responses WHERE question_label = "Q2B" AND value IN (1,8,9))

AND

(response_no, survey_id, interview_date) IN (SELECT DISTINCT response_no, survey_id, interview_date FROM responses WHERE question_label = "Q2C" AND value IN (1,8,9))

AND

(response_no, survey_id, interview_date) IN (SELECT DISTINCT response_no, survey_id, interview_date FROM responses WHERE question_label = "Q2D" AND value IN (1,8,9))

AND

(response_no, survey_id, interview_date) IN (SELECT DISTINCT response_no, survey_id, interview_date FROM responses WHERE question_label = "Q2E" AND value IN (1,8,9))

AND

(response_no, survey_id, interview_date) IN (SELECT DISTINCT response_no, survey_id, interview_date FROM responses WHERE question_label = "Q2F" AND value IN (1,8,9))

AND

(response_no, survey_id, interview_date) IN (SELECT DISTINCT response_no, survey_id, interview_date FROM responses WHERE question_label = "Q2G" AND value IN (1,8,9))

AND

(response_no, survey_id, interview_date) IN (SELECT DISTINCT response_no, survey_id, interview_date FROM responses WHERE question_label = "Q2H" AND value IN (1,8,9))

AND

(response_no, survey_id, interview_date) IN (SELECT DISTINCT response_no, survey_id, interview_date FROM responses WHERE question_label = "Q2I" AND value IN (1,8,9))

AND

(response_no, survey_id, interview_date) IN (SELECT DISTINCT response_no, survey_id, interview_date FROM responses WHERE question_label = "Q5" AND value IN (1,8,9))

AND

(response_no, survey_id, interview_date) IN (SELECT DISTINCT response_no, survey_id, interview_date FROM responses WHERE question_label = "Q6" AND value IN (1,8,9))

结果，运行一次，用了12秒，太慢了。

请教mysql高手，有没有办法能加快计算速度。