mysql - 赋予 mysql 搜索结果权重

Question

我正在尝试通过按最匹配的记录对记录进行排序来改进我的网站之一中的搜索功能。

我有以下 mysql 表。

调查回复

+--------+-------------+------------------+
| sID    |    Title    |  Description     |
+--------+-------------+------------------+
| 1      |  Set    1   | Some txt here    |
| 2      |  Set    2   | Other text here  |
+--------+-------------+------------------+

答案:

+------+---------+------------+---------+
| aID  |  Parent |  Question  | Answer  |
+------+---------+------------+---------+
| 1    |  1      |      1     |  yes    | 
| 2    |  1      |      2     |  yes    |
| 3    |  1      |      3     |  yes    |
| 4    |  2      |      1     |  yes    |
| 5    |  2      |      2     |  no     |
| 6    |  2      |      3     |  no     |
+------+---------+------------+---------+

我想从 SurveyResponses 表中获取所有记录，并按照答案表中正确匹配的问题的顺序对它们进行排序。正确答案如下:

Q1 = yes
Q2 = no
Q3 = no

因此，基于此，查询应在结果顶部返回 Set 2，因为该问题的答案表中的答案全部正确，而 Set 1 中的 q2 和 q3 是错误的。

我想我需要某种评分系统，这样我就可以做类似的事情

IF q1 = yes THEN score = score + 1 

显然我可以在 PHP 中做到这一点，但不确定在 mysql 中使用什么方法。

如果能回答这个问题，我们将不胜感激，但即使是最好方法的提示或链接也将不胜感激。

Answer 1

我不太理解你的设计，或者你想要做什么，相反，我会提供一个可能有帮助也可能没有帮助的替代示例......

CREATE TABLE survey
(question_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,question VARCHAR(100) NOT NULL UNIQUE
,correct_answer VARCHAR(3) NOT NULL
);

INSERT INTO survey VALUES
(1,'Is Rome the capital of Italy?','Yes'),
(2,'Is Paris the capital of England?','No'),
(3,'Is London the capital of Spain?','No');

CREATE TABLE survey_response
(user_id INT NOT NULL
,question_id INT NOT NULL
,response VARCHAR(3) NOT NULL
,PRIMARY KEY(user_id,question_id)
);

INSERT INTO survey_response VALUES
(101,1,'Yes'),
(101,2,'Yes'),
(101,3,'Yes'),
(102,1,'Yes'),
(102,2,'No'),
(102,3,'No');

SELECT * FROM survey;
+-------------+----------------------------------+----------------+
| question_id | question                         | correct_answer |
+-------------+----------------------------------+----------------+
|           1 | Is Rome the capital of Italy?    | Yes            |
|           2 | Is Paris the capital of England? | No             |
|           3 | Is London the capital of Spain?  | No             |
+-------------+----------------------------------+----------------+

SELECT * FROM survey_response;
+---------+-------------+----------+
| user_id | question_id | response |
+---------+-------------+----------+
|     101 |           1 | Yes      |
|     101 |           2 | Yes      |
|     101 |           3 | Yes      |
|     102 |           1 | Yes      |
|     102 |           2 | No       |
|     102 |           3 | No       |
+---------+-------------+----------+

假设我想根据谁得到了最正确的答案对受访者 (user_ids) 进行排序。我可以这样做...

SELECT sr.user_id 
  FROM survey s 
  JOIN survey_response sr 
    ON sr.question_id = s.question_id 
 GROUP 
    BY user_id 
 ORDER 
    BY SUM(sr.response = s.correct_answer) DESC;
+---------+
| user_id |
+---------+
|     102 |
|     101 |
+---------+