获取出现次数最多的外键

我有以下表格。

user

option

question

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;

这给了我以下结果

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

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

该怎么办呢？

UPDATE:

这个查询给了我最接近的结果，

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 的最后一次出现，参考下图中的内容。

它给了我以下结果

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

而我期望以下内容

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

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

可以使用临时表逐步解决：

-- get counts for each user and option
DROP TEMPORARY TABLE IF EXISTS tmp_selected_options;
CREATE TEMPORARY TABLE tmp_selected_options
    SELECT a.user_id, a.option_id, COUNT(a.option_id) AS numSelected
    FROM answer a
    GROUP BY a.user_id, a.option_id
;
select * from tmp_selected_options;

-- get the max number an option was selected for each user
DROP TEMPORARY TABLE IF EXISTS tmp_max_selected;
CREATE TEMPORARY TABLE tmp_max_selected
    SELECT so.user_id, MAX(so.numSelected) AS maxSelected
    FROM tmp_selected_options so
    GROUP BY so.user_id
;
select * from tmp_max_selected;

-- get otions that were selected most for each user
DROP TEMPORARY TABLE IF EXISTS tmp_most_selected_otions;
CREATE TEMPORARY TABLE tmp_most_selected_otions
    SELECT so.user_id, so.option_id
    FROM tmp_max_selected ms
    JOIN tmp_selected_options so
        ON  so.user_id = ms.user_id
        AND so.numSelected = ms.maxSelected
;
select * from tmp_most_selected_otions;

-- get least option-id of most selected options for each user
SELECT u.id AS user_id, MIN(mso.option_id) AS option_id
FROM user u
LEFT JOIN tmp_most_selected_otions mso ON mso.user_id = u.id
GROUP BY u.id
;

Notes:

• 您可能需要添加一些键以提高性能。
• 如果用户选择两个选项的次数相同，则将选择 ID 最少的选项。

将最后一个语句中的临时表替换为相应的选择，您将得到以下查询：

-- get least option-id of most selected options for each user
SELECT u.id AS user_id, MIN(mso.option_id) AS option_id
FROM user u
LEFT JOIN ( -- get otions that were selected most for each user
    SELECT so.user_id, so.option_id
    FROM ( -- get the max number an option was selected for each user
        SELECT so.user_id, MAX(so.numSelected) AS maxSelected
        FROM ( -- get counts for each user and option
            SELECT a.user_id, a.option_id, COUNT(a.option_id) AS numSelected
            FROM answer a
            GROUP BY a.user_id, a.option_id
        ) so
        GROUP BY so.user_id 
    ) ms
    JOIN ( -- get for counts each user and option
        SELECT a.user_id, a.option_id, COUNT(a.option_id) AS numSelected
        FROM answer a
        GROUP BY a.user_id, a.option_id
    ) so
        ON  so.user_id = ms.user_id
        AND so.numSelected = ms.maxSelected
) mso ON mso.user_id = u.id
GROUP BY u.id

http://sqlfiddle.com/#!9/c2b20/2 http://sqlfiddle.com/#!9/c2b20/2