获取外键出现次数最多 [英] Getting foreign key with maximum occurrences

问题描述

我有下表.

用户

选项

问题

answer

我想获取以下记录

1. 按用户分组的答案数
2. 按用户分组的回答最多的选项

我尝试使用以下查询

SELECT
    u.id,
    u.email,
    COUNT(a.question_id) as number_of_answers
FROM
    user u
LEFT JOIN
    answer a ON (a.user_id = u.id)
GROUP BY
    u.id;

哪个给我以下结果

我现在想获取每个用户最常用的选项，参考上面的图片，我期望得到以下结果

I now want to fetch most used option per user, with reference to above image, I am expecting the following result

-----------------------
user_id  |   option_id
-----------------------
1        |   null
2        |   3
3        |   null
-----------------------

如何去做?

更新:

此查询为我提供了最接近的结果

This query gives me the closest result,

SELECT
    u.id,
    u.email,
    COUNT(a.question_id) as number_of_answers,
    (SELECT a2.option_id FROM answer a2 WHERE a2.user_id = u.id GROUP BY a2.option_id ORDER BY COUNT(a2.option_id) DESC LIMIT 1) as option_id
FROM
    user u
LEFT JOIN
    answer a ON (a.user_id = u.id)
GROUP BY
    u.id;

现在的问题是，它没有按我期望的顺序排列option_id，如果最大出现次数相同，我要获取option_id的最后一次出现，请参考下图中的内容.

The issue now is it does not order option_id as I expect, I want to get last occurrence of option_id if there are same number of maximum occurrences, with reference to content in below image.

它给我以下结果

+-----------+-----------+
| option_id | occurence |
+-----------+-----------+
|         5 |         2 |
+-----------+-----------+

我希望以下内容

+-----------+-----------+
| option_id | occurence |
+-----------+-----------+
|         1 |         2 |
+-----------+-----------+

原因是，option_id是最后记录的选项.关于我在这里缺少什么的任何提示?

The reason being, option_id is the last recorded option. any hint on what I am missing here?

推荐答案

SELECT u.id, u.email, max(p.opt_count) occurances , p.option_id from user u 
JOIN (
  SELECT u.id, count(a.question_id) qc, a.option_id, count(a.option_id) opt_count 
  FROM user u
  LEFT JOIN answer a on u.id=a.user_id 
      GROUP by u.id, a.question_id, a.option_id  
 ) as p 
 ON u.id=p.id group by u.id ;

这篇关于获取外键出现次数最多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！