mysql - 查询结果集按结果/匹配项的最大数量降序排列

Question

我正在开发一个符合用户和群组兴趣的功能。根据我的查询结果，我能够获得匹配项，但是我需要帮助根据结果最多的 group_id 按降序对结果进行排序。

例如，根据下面的结果集，我想像这样对其进行排序... group_id 47 将位于顶部，因为它有 4 个结果，其次是 group_id 44 和 group_id 48，有 2 个结果，而 group_id 48 有 1 个结果例如 group_id 的 40、42、43 和 49 将位于底部。

这是示例查询。

SELECT * FROM `group_questionnaire_answers` 
WHERE (question_id = 1 AND answer =3)
OR (question_id = 2 AND answer =1)
OR (question_id = 3 AND answer =4)
OR (question_id = 4 AND answer =4)
OR (question_id = 5 AND answer =4)
OR (question_id = 6 AND answer =3)
OR (question_id = 7 AND answer =3)
OR (question_id = 8 AND answer =4)
OR (question_id = 9 AND answer =5)
OR (question_id = 10 AND answer =2)

这是示例结果集。

Answer 1

这取决于您想要的方式:

SELECT group_id, count(*) AS num
FROM group_questionnaire_answers
WHERE ...
GROUP BY 1 ORDER BY 2 DESC

如果要显示所有详细信息，则需要使用自连接:

SELECT a.*
FROM group_questionnaire_answers AS a
JOIN (
    SELECT group_id, count(*) AS num
    FROM group_questionnaire_answers
    WHERE ...
    GROUP BY 1
) AS b ON a.group_id = b.group_id
WHERE ...
ORDER BY b.num DESC

WHERE ... === 你原来的 SQL where