我试图找出用户回答最多的语言，并通过 user_id 返回，他们回答最多的 language_id 以及他们回答了多少次答案。

我首先SELECTing 一个返回这些结果的表/子表:

Table: `sub-selected`
`user_id`    `language_id`    `answers`
  1               1               1
  2               1               1
  1               2               5
  2               2               2
  1               4               3
  1               5               1

此表返回 user_id、language_id 以及用户回答该 language_id 的次数。我使用这个查询来获取它:

SELECT t1.user_id, t2.to_language_id, COUNT(t2.to_language_id) as answers
FROM translation_results as t1
LEFT JOIN translations as t2
ON t2.translation_id = t1.translation_id
GROUP BY t2.to_language_id, t1.user_id

表结构为:

Table: `translations`
`translation_id`    `from_phrase_id`    `to_language_id`

Table: `translation_results`
`translation_id`    `result_id` PRI-AI    `user_id`

translations 表存储所有请求的翻译，translation_results 表存储这些翻译的答案和相应的 user_id。

因此，为了汇总表格并获取 user_id、他们回答最多的 language_id 以及他们用该 language_id 回答的次数，我使用了:

SELECT t1.user_id, t1.to_language_id, MAX(t1.answers)
FROM (
    //The sub-table
    SELECT t1.user_id, t2.to_language_id, COUNT(t2.to_language_id) as answers
    FROM translation_results as t1
    LEFT JOIN translations as t2
    ON t2.translation_id = t1.translation_id
    GROUP BY t2.to_language_id, t1.user_id
) as t1
GROUP BY t1.user_id, t1.to_language_id

但这不会将表折叠成所需的结构，而是返回:

Table: `sub-selected`
`user_id`    `language_id`    `answers`
  1               1               1
  1               2               5
  1               4               3
  1               5               1
  2               1               1
  2               2               2

我知道它受 两个子句 的分组影响，但是如果我只按 user_id 分组并且在我选择的列中不包含 to_language_id，我无法知道各自的 language_id 是哪个回答最多。我也尝试过子查询和一些连接，但我发现我经常需要使用 MAX(t1.answers) 而不管在选定的列中，因此破坏了我折叠 组的希望通过 正确。我怎样才能正确折叠查询，而不是让 group by 找到 user_id 和 to_language_idMAX() 组合?

最佳答案

得到:

the user_id, their most answered language_id, and how many times they answered in that language_id

你可以使用变量:

SELECT user_id, language_id, answers
FROM (
  SELECT user_id, language_id, answers,
         @rn:= IF(@uid = user_id,
                  IF(@uid:=user_id, @rn:=@rn+1, @rn:=@rn+1),
                  IF(@uid:=user_id, @rn:=1, @rn:=1)) AS rn
  FROM (SELECT t1.user_id, t2.to_language_id AS language_id, 
               COUNT(t2.to_language_id) as answers     
        FROM translation_results as t1 
        LEFT JOIN translations as t2 
           ON t2.translation_id = t1.translation_id
        GROUP BY t2.to_language_id, t1.user_id 
       ) t
  CROSS JOIN (SELECT @rn:=0, @uid:=0) AS vars
  ORDER BY user_id, answers DESC
) s
WHERE s.rn = 1

但是在上面的查询中有一个限制:如果有多个 language_id 对 user_id 共享相同的最大答案数，那么只有一个会是返回。

Demo here

另一种方法是将您查询两次作为派生表:

SELECT t1.user_id, language_id, t1.answers
FROM (SELECT t1.user_id, t2.to_language_id AS language_id, 
             COUNT(t2.to_language_id) as answers
      FROM translation_results as t1
      LEFT JOIN translations as t2
         ON t2.translation_id = t1.translation_id
      GROUP BY t2.to_language_id, t1.user_id ) t1
INNER JOIN (      
   SELECT user_id, MAX(answers) AS answers
   FROM (SELECT t1.user_id, t2.to_language_id, 
                COUNT(t2.to_language_id) as answers
         FROM translation_results as t1
         LEFT JOIN translations as t2
            ON t2.translation_id = t1.translation_id
         GROUP BY t2.to_language_id, t1.user_id 
        ) t
   GROUP BY user_id ) t2
ON t1.user_id = t2.user_id AND t1.answers = t2.answers 

此查询没有上一个查询的限制，但与上一个查询相比可能效率较低。

Demo here

关于mysql - 在同一查询中使用 MAX() 和 COUNT()，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/31228519/