查询以从每个问题的答案集中选择已回答所有问题的用户

Question

这是mysql表。

            User    Question    Answer
            20      1           answer1_2
            20      2           answer2_5
            20      3           answer3_1
            726     1           answer1_2
            726     2           answer2_5
            726     3           answer3_1

我希望用户回答所有问题和我们为每个问题都有预定义的答案集。

就像问题 1 可以从answer1_1、answer1_2 answer1_3 问题 2 可以从answer2_1、answer2_2 answer2_3

最初，我尝试了这样的查询：

            SELECT GROUP_CONCAT( DISTINCT CAST( User AS CHAR ) ) allUsers, COUNT( DISTINCT User ) totalUsers
            FROM survey
            WHERE
            (
                (
                Question =1
                AND Answer =  'answer1_2'
                )
                OR (
                Question =1
                AND Answer =  'answer1_2'
                )
                OR (
                Question =1
                AND Answer =  'answer1_3'
                )
            )
            AND (
                (
                Question =2
                AND Answer =  'answer2_1'
                )
                OR (
                Question =2
                AND Answer =  'answer2_2'
                )
                OR (
                Question =2
                AND Answer =  'answer2_3'
                )
            )

但后来我看到结果并意识到所有问题中的"AND"不适合我的要求但我需要具有该逻辑的结果。

无法更改表结构。

Answer 1

你可以做这样的事情：

Select user, count(answer) from survey group by user having count(answer)=3

Answer 2

如果您在字段 User 和 Question 上有一个唯一的键，那么您可以执行以下操作：

SELECT count(Question) answers_no, .... FROM survey GROUP BY User HAVING answers_no = num_total_questions

您可以将num_total_questions作为应用程序中的变量传递