基于列匹配的Mysql查询

Question

I don’t know how to make this with mysql, I only know how to do basic queries, I want to show a list of result based on matches, Results will be shown based on the same value of the answers…answers may have a value from 1-10

+++++++++++++++TABLE++++++++++++

id  | userName | answer1 | answer2 | answer3 | answer4….

10    Jhon       1        1         3          8

11    Anne       1        2         4          8

12    Mike       7        4         5          7

etc…
++++++++++++++++++++++++++++++++++++++++

If I send the values in the query I want that check the answers and show the result sorted my matches, more matches first…no matches last

So if i send the results:

answer1=1  answer2=1 answer3=7 answer4=2...

the result should be(give back the id)

10 11 12

Answer 1

Your table design is not fine, you should separate it into users and questions table.

If you cannot change the table design you can solve you problem using this query:

select
  id,
  username,
  if(answer1 = :an1, 1, 0) + if(answer2 = :an2, 1, 0) + if(answer3 = :an3, 1, 0) + if(answer4 = :an4, 1, 0) as total
from
  table
order by total desc

UPDATE: Better design for this problem:

Check the SQLFiddle: http://sqlfiddle.com/#!9/6c145/2 with a live demo.

1. Create Users Table

   CREATE TABLE users (
       id INT AUTO_INCREMENT PRIMARY KEY,
       username VARCHAR(50) NOT NULL
   );
   

2. Create Questions Table

   CREATE TABLE questions (
       id INT PRIMARY KEY,
       correct_answer INT NOT NULL
   );
   

3. Create User Answers Table

    CREATE TABLE user_answers (
        user_id INT,
        question_id INT,
        user_answer TINYINT,
        PRIMARY KEY (user_id, question_id),
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE NO   ACTION ON UPDATE NO ACTION,
        FOREIGN KEY (question_id) REFERENCES questions (id) ON DELETE NO ACTION ON UPDATE NO ACTION
    );
   

Than to retrieve the data you can use the query:

SELECT
    tmp.id,
    tmp.username,
    sum(tmp.is_correct) as total
FROM (
    SELECT
        users.id,
        users.username,
        IF (questions.correct_answer = user_answers.user_answer, 1, 0) as is_correct
    FROM
        users
        INNER JOIN user_answers on users.id = user_answers.user_id
        INNER JOIN questions on user_answers.question_id = questions.id
) tmp
GROUP BY tmp.id, tmp.username
ORDER BY total desc;