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

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 作为应用程序中的变量传递