比较MySQL中的两个子查询

Question

I'm looking to find the exact-matches columns that have the same answers to a set of the same questions. Their answers may vary, and I want to find the ones where two people answered the same, and I want to get their unique response ID.

SELECT 
  response 
FROM 
  responses_questions 
WHERE (
  SELECT 
    answer 
  FROM 
    responses_questions 
  WHERE 
    response = '$my_response_id' 
  GROUP BY 
    question 
  ORDER BY 
    question 
  ASC
) = (
  SELECT 
    answer 
  FROM 
    responses_questions 
  GROUP BY 
    question 
  ORDER BY 
    question 
  ASC
)

I'm completely aware that the query above will NOT work.

The primary problem is that I need to compare the "criteria query" (consists of several rows) to the rest of the database to return the response ID for the matching groups of columns. Basically, for each question, I need all of their answers to be the same as the "example" query that we are comparing to. "Comparing two subqueries".

I can easily visualize the data I need to get; Nonetheless, SQL doesn't take into account what us humans "visualize"...

If I need to provide more information, please let me know in the comments below.

EDIT: Screenshot/More Data

Here is a screenshot of some example data:

The highlighted data is what we are SELECTING in the "first" part of my query above. I need to find all of the data that MATCHES.

My expected output should be the following numbers from the response column: 5 and 7. But not 6.

Keep in mind: There will be hundreds of questions, so a lot more rows to "compare". Therefore, don't hard-code any specific question numbers in.

Answer 1

EDITED

I realized on my first answer attempt, I misread your question. I think you are trying to find which people got 100%, right? Let me know if I'm wrong.

SELECT b.response
FROM responses_questions b
LEFT JOIN responses_questions a 
    ON a.question = b.question
    AND a.answer = b.answer
    AND a.response = '$my_response_id'
    AND a.response != b.response
GROUP BY b.response
HAVING COUNT(DISTINCT a.question) = COUNT(DISTINCT b.question)

This works by grouping by each person to get the set of each person's questions and answers. We join to a version of the table that only has the "example" response id you were speaking of, and we join such that the questions and the answers both match. We use a left join so that any non-matching entries will have a NULL on the a table. Finally, we count the number of entries in each table. NULL entries don't add to the count, so if there are any non-matching rows, the count of the a table will be less than the count of the b table.

BTW, you are making me wary with your '$my_response_id' being stuck in the middle there. Better to use prepared queries. I hope you are sanitizing your inputs. -- Yay!

EDIT

I added a check to make sure it would not also return the "example" id of 4. It should work fine. I did a sqlfiddle and got 5 and 7 just like you requested.