获得最大出现次数的外键 [英] Getting foreign key with maximum occurences

问题描述

我有以下表格。

用户

问题

回答

我要获取以下记录

1. 按用户分组的答案数量


2. 按用户分组的最常回答的选项

我尝试使用以下查询

  SELECT 
 u.id，
 u.email，
 COUNT（a.question_id）as number_of_answers 
 FROM 
用户u 
 LEFT JOIN 
回答ON a.user_id = u.id）
 GROUP BY 
 u.id; 
  

这给了我下面的结果

a href =https://i.stack.imgur.com/kcuTl.png =nofollow noreferrer>

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

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

如何处理？

感谢。

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

  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 
用户u 
 LEFT JOIN 
回答ON（a.user_id = u .id）
 GROUP BY 
 u.id; 
  

现在的问题是它不会按期望订单option_id，我想获得option_id的最后一次出现

它给我以下结果

  + ---------- -  + ----------- + 
 | option_id |发生
 + ----------- + ----------- + 
 | 5 | 2 | 
 + ----------- + ----------- + 
  

我期望下面的

  + ----------- + ----------- + 
 | option_id |发生
 + ----------- + ----------- + 
 | 1 | 2 | 
 + ----------- + ----------- + 
  

原因是，option_id是最后一个记录的选项。

解决方案

  SELECT u.id，u。电子邮件，最大（p.opt_count）发生，p.option_id从用户u 
 JOIN（
 SELECT u.id，count（a.question_id）qc，a.option_id，count（a.option_id）opt_count 
 FROM user u 
 LEFT JOIN回答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; 
  

I have following following tables.

user

option

question

answer

I want to fetch the following records

1. Number of answers grouped by user
2. Most answered option grouped by user

I tried using following query

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;

Which gives me following result

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
-----------------------

how to go about it?

Thanks.

UPDATE :

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;

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.

It gives me the following result

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

Whereas i expect the following

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

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屋！