All geek questions in one place

MySQL query comparing rows and columns

I have a database of user responses from a quiz that contains 8 questions. Q1 - Q8 - all their own columns, I would like to compare all the rows and get a number for everyone who answered the same, at least 5 questions.

enter image description here

so here lines 5 and 6 will be counted as 2. Basically, I'm trying to get a number for everyone who answered at least 5 questions the same way. Is this possible with a mySQL query?

EDIT:

enter image description here

Here the user enters DBDABCDB, matching with two similarly responding polls. The query here will return counter 2.

+4
source share
2 answers

If we check using only one DBDABCDB row, we can use the following example:

SELECT * FROM `answers` WHERE ((CASE WHEN q1 = 'D' THEN 1 ELSE 0 END) + (CASE WHEN q2 = 'B' THEN 1 ELSE 0 END) + (CASE WHEN q3 = 'D' THEN 1 ELSE 0 END) + (CASE WHEN q4 = 'A' THEN 1 ELSE 0 END) + (CASE WHEN q5 = 'B' THEN 1 ELSE 0 END) + (CASE WHEN q6 = 'C' THEN 1 ELSE 0 END) + (CASE WHEN q7 = 'D' THEN 1 ELSE 0 END) + (CASE WHEN q8 = 'B' THEN 1 ELSE 0 END)) >= 5;

However, if we want to take one more step and test each answer to the other answers in the table, we can use the following statement:

 SELECT *, (SELECT COUNT(answer_sub.idanswers) FROM `answers` answer_sub WHERE ((CASE WHEN answer_sub.q1 = a.q1 THEN 1 ELSE 0 END) + (CASE WHEN answer_sub.q2 = a.q2 THEN 1 ELSE 0 END) + (CASE WHEN answer_sub.q3 = a.q3 THEN 1 ELSE 0 END) + (CASE WHEN answer_sub.q4 = a.q4 THEN 1 ELSE 0 END) + (CASE WHEN answer_sub.q5 = a.q5 THEN 1 ELSE 0 END) + (CASE WHEN answer_sub.q6 = a.q6 THEN 1 ELSE 0 END) + (CASE WHEN answer_sub.q7 = a.q7 THEN 1 ELSE 0 END) + (CASE WHEN answer_sub.q8 = a.q8 THEN 1 ELSE 0 END)) >= 5 AND answer_sub.idanswers <> a.idanswers) as matching FROM `answers` a WHERE (SELECT COUNT(answer_sub.idanswers) FROM `answers` answer_sub WHERE ((CASE WHEN answer_sub.q1 = a.q1 THEN 1 ELSE 0 END) + (CASE WHEN answer_sub.q2 = a.q2 THEN 1 ELSE 0 END) + (CASE WHEN answer_sub.q3 = a.q3 THEN 1 ELSE 0 END) + (CASE WHEN answer_sub.q4 = a.q4 THEN 1 ELSE 0 END) + (CASE WHEN answer_sub.q5 = a.q5 THEN 1 ELSE 0 END) + (CASE WHEN answer_sub.q6 = a.q6 THEN 1 ELSE 0 END) + (CASE WHEN answer_sub.q7 = a.q7 THEN 1 ELSE 0 END) + (CASE WHEN answer_sub.q8 = a.q8 THEN 1 ELSE 0 END)) >= 5 AND answer_sub.idanswers <> a.idanswers) > 0
+2
source

Because FALSE is 0 and TRUE is 1 in MySQL:

 SELECT COUNT(*) FROM quiz WHERE ( ( q1=@q1 ) + ( q2=@q2 ) + ( q3=@q3 ) + ( q4=@q4 ) + ( q5=@q5 ) + ( q6=@q6 ) + ( q7=@q7 ) + ( q8=@q8 ) ) >= 5
0
source

Source: https://habr.com/ru/post/1391729/

More articles:


All Articles