mysql 查询 - 重复结果的问题

Question

我有这个查询:

SELECT DISTINCT u.name_surname, u.avatar, u.location
FROM users AS u
JOIN connections AS c
  ON c.user_id = u.id
JOIN words_en AS w 
  ON w.id = c.word_id
LEFT JOIN friends AS f1
  ON f1.asker_user_id = :user_id
    AND f1.status = 1
    AND f1.asked_user_id = u.id
WHERE (w.word = :kwd
  OR u.location = :kwd
  OR u.name_surname = :kwd)
AND (u.privacy = 3 OR (u.privacy = 2 and f1.id IS NOT NULL))
AND c.deleted <> 1

我想了解 kwd 的匹配来自哪里(哪一列)，

所以我在选择中添加了这个:

/*,
    CONCAT_WS(",",
        CASE WHEN w.word = :kwd THEN "word" END,
        CASE WHEN u.location = :kwd THEN "location" END,
        CASE WHEN u.name_surname = :kwd THEN "name_surname" END) As matches*/

添加这个之后的问题是，只要有双重匹配，比方说位置和名称，就会显示两个结果。我该如何解决这个问题？

Answer 1

尝试对这个附加字段使用 GROUP BY 而不是 DISTINCT 和 MAX:

SELECT u.name_surname, u.avatar, u.location, 
       MAX(CONCAT_WS(",",
        CASE WHEN w.word = :kwd THEN "word" END,
        CASE WHEN u.location = :kwd THEN "location" END,
        CASE WHEN u.name_surname = :kwd THEN "name_surname" END)) As matches
FROM users AS u
JOIN connections AS c
  ON c.user_id = u.id
JOIN words_en AS w 
  ON w.id = c.word_id
LEFT JOIN friends AS f1
  ON f1.asker_user_id = :user_id
    AND f1.status = 1
    AND f1.asked_user_id = u.id
WHERE (w.word = :kwd
  OR u.location = :kwd
  OR u.name_surname = :kwd)
AND (u.privacy = 3 OR (u.privacy = 2 and f1.id IS NOT NULL))
AND c.deleted <> 1

GROUP BY u.name_surname, u.avatar, u.location