mysql - 解密我对mysql语句的匹配

当我也不想包括分数时，它会起作用。但是，当然这对于搜索结果的排序很重要。这不起作用：

SELECT * from
          ( SELECT distinct ts.users_user_id from subjects s
          JOIN teachers_subjects ts ON s.subject_ID = ts.subject_id, MATCH  (s.subject_en_name) AGAINST ('math') AS score
          WHERE MATCH (s.subject_en_name) AGAINST ("math") ) qualified
          JOIN users u ON qualified.users_user_id = u.user_id
          JOIN users_teachers ut ON qualified.users_user_id = ut.user_id
          JOIN teachers_subjects ts2 ON qualified.users_user_id = ts2.users_user_id
          JOIN subjects s2 ON ts2.subject_id = s2.subject_id

这有效，但不包括每一行的分数：

SELECT * from
          ( SELECT distinct ts.users_user_id from subjects s
          JOIN teachers_subjects ts ON s.subject_ID = ts.subject_id
          WHERE MATCH (s.subject_en_name) AGAINST ("math") ) qualified
          JOIN users u ON qualified.users_user_id = u.user_id
          JOIN users_teachers ut ON qualified.users_user_id = ut.user_id
          JOIN teachers_subjects ts2 ON qualified.users_user_id = ts2.users_user_id
          JOIN subjects s2 ON ts2.subject_id = s2.subject_id

最佳答案

问题是score应该是SELECT而不是FROM子句的一部分。这样尝试

SELECT *
  FROM
(
  SELECT DISTINCT ts.users_user_id,
         MATCH (s.subject_en_name) AGAINST ('math') AS score
    FROM subjects s JOIN teachers_subjects ts
      ON s.subject_ID = ts.subject_id
   WHERE MATCH (s.subject_en_name) AGAINST ('math')
) q JOIN users u
    ON q.users_user_id = u.user_id JOIN users_teachers ut
    ON q.users_user_id = ut.user_id JOIN teachers_subjects ts2
    ON q.users_user_id = ts2.users_user_id JOIN subjects s2
    ON ts2.subject_id = s2.subject_id
 ORDER BY q.score DESC