英文:
Can i use multiple and condition or where in grouping in mysql
问题
以下是翻译的内容：
这是我的表结构和数据，如下所示：
我需要筛选出医生和用户组合的数据（按组分组），满足条件：答案是"Male"和"Below 30"。
我尝试了以下查询，但效果不理想：
-
select * from doctor_classification_question_answers where (question_id = 11 and answer like '["Female"]') and (question_id = 12 and answer like '["Below 30"]') group by user_id, doctor_id; -
select * from doctor_classification_question_answers where (question_id, answer) in ((11, '["Male"]'), (12, 'Below 30')) group by user_id, doctor_id
还有许多其他类型的答案，涉及不同的问题组合。如果有人可以帮忙，我想要一个满足给定条件的唯一user_id和doctor_id组合。
英文:
Here is my table structure and data as
I need a data to filter like, get list of doctor, user combination (group by) where match both condition like ANSWER is "Male" and "Below 30".
i have tried below query but not working perfecly.
-
select * from doctor_classification_question_answers where (question_id = 11 and answer like '["Female"]') and (question_id = 12 and answer like '["Below 30"]') group by user_id, doctor_id; -
select * from doctor_classification_question_answers where (question_id,answer) in ((11,'["Male"]'),(12,'Below 30')) group by user_id, doctor_id
there is many other type of answer which combination of question. if anyone can help.
I want a unique user_id and doctor_id combination who fulfill given condition.
答案1
得分: 2
有几种方法可以实现这个，取决于您的表设置。这是自连接版本：
SELECT DISTINCT a1.user_id, a1.doctor_id
FROM doctor_classification_answers a1
INNER JOIN doctor_classification_answers a2 ON a1.user_id = a2.user_id AND a1.doctor_id = a2.doctor_id
WHERE a1.question_id = 11 AND a1.answer_id = ''["Female"]''
AND a2.question_id = 12 AND a2.answer_id = ''["Below 30"]'';
或者，如果您想使用GROUP BY/HAVING子句，可以尝试以下方法：
SELECT user_id, doctor_id
FROM doctor_classification_answers
WHERE question_id = 11 AND answer_id = ''["Female"]''
OR question_id = 12 AND answer_id = ''["Below 30"]''
GROUP BY user_id, doctor_id
HAVING COUNT(*) = 2;
（假设您的user_id、doctor_id和question_id上有唯一键）
选择哪种方法将取决于您的表结构和数据。建议对每种方法进行准确性和性能测试。
英文:
There are a couple of ways to do this, depending on your table setup. This is the self-join version:
SELECT DISTINCT a1.user_id, a1.doctor_id
FROM doctor_classification_answers a1
INNER JOIN doctor_classification_answers a2 ON a1.user_id = a2.user_id AND a1.doctor_id = a2.doctor_id
WHERE a1.question_id = 11 AND a1.answer_id = '["Female"]'
AND a2.question_id = 12 AND a2.answer_id = '["Below 30"]'
or if you want to use a GROUP BY/HAVING clause, this might suit
SELECT user_id, doctor_id
FROM doctor_classification_answers
WHERE question_id = 11 AND answer_id = '["Female"]'
OR question_id = 12 AND answer_id = '["Below 30"]'
GROUP BY user_id, doctor_id
HAVING COUNT(*) = 2
(presuming you have a unique key on user_id, doctor_id and question_id)
Which you choose will depend on your table structure and data. I would recommend testing each for accuracy and performance
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论