mysql选择查询

Question

我有 2 个表:interviews 和 interview_keywords。

一次采访有 5 个排序的关键字。我需要在排序列表的正确位置使用指定关键字的采访列表。这是我到目前为止所得到的，但没有用:

   SELECT i.id, 
          i.title
     FROM interviews AS i 
LEFT JOIN interview_keywords AS ik ON i.id = ik.interview_id 
    WHERE i.cat_id = 1 
      AND ( (ik.keyword_id = 39 AND ik.sort = 1) 
        AND (ik.keyword_id = 33 AND ik.sort = 2) 
        AND (ik.keyword_id = 51 AND ik.sort = 3) 
        AND (ik.keyword_id = 96 AND ik.sort = 4) 
        AND (ik.keyword_id = 97 AND ik.sort = 5)) 

Answer 1

SELECT i.id, i.title
FROM interviews i
INNER JOIN interview_keywords ik1
    ON ik1.interview.id = i.id
    AND ik.keyword_id = 39
    AND ik1.sort = 1
INNER JOIN interview_keywords ik2
    ON ik2.interview.id = i.id
    AND ik2.keyword_id = 33
    AND ik2.sort = 2
INNER JOIN interview_keywords ik3
    ON ik3.interview.id = i.id
    AND ik3.keyword_id = 51
    AND ik3.sort = 3
INNER JOIN interview_keywords ik4
    ON ik4.interview.id = i.id
    AND ik4.keyword_id = 96
    AND ik4.sort = 4
INNER JOIN interview_keywords ik5
    ON ik5.interview.id = i.id
    AND ik5.keyword_id = 97
    AND ik5.sort = 5
WHERE i.cat_id = 1