SELECT多行WHERE匹配两个条件 [英] SELECT multiple rows WHERE matching two conditions

问题描述

最好在我的示例中显示:

This would be better to show on my example:

我有一张桌子，存储着用户从一种大表格中得到的答案.每个表格有139个问题.这些问题存储在不同的表中，并在需要时与questionID结合在一起.对于每个用户，都有一个ID.我现在需要进行过滤，以仅显示与特定问题匹配一个或更多答案的用户.

I have table, where are stored users answers from one big form. Each form has 139 questions. These questions are stored in different table, joined when needed with questionID. For each user, there is an ID. I now need to make filters, to show only users matching one or more answers on specific questions.

例如，我希望用户在问题14中回答是"，问题54不为空，问题100大于10.这是表格的外观:

For example, i want users, where question 14 has answer "yes", question 54 is not empty and question 100 is bigger than 10. This is how the table looks:

**userID** | **questionID** | **answer**

1            14               "yes"
1            54               "something"
1            100              "9"
2            14               "no"
2            54               "north
2            100              "50"
3            14               "yes"
3            54               "test"
3            100              "12"

结果是，我只希望返回 userID 3，因为它满足所有条件.

as result i want only the userID 3 returned, because it meets all conditions.

使用ColdFusion可以轻松实现，因为它允许查询查询的结果，但是在PHP中我找不到任何方法.有机会添加随机数量的问题很重要，在这个示例中不仅要添加三个问题.

This would be easy to reach with ColdFusion, as it allows query in queried results, but in PHP i haven't found any way. It is important to have chance to add random number of questions, not only three as in this example.

推荐答案

尝试

SELECT userID
FROM tableName
WHERE   (questionID = 14 AND
        answer = 'yes' ) OR
        (questionID = 54 AND
        answer <> 'empty') OR
        (questionid = 100 AND
        answer > 10)
GROUP BY userID
HAVING COUNT(*) = 3

这篇关于SELECT多行WHERE匹配两个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！