首页 文章

MySQL - 从子查询组中获取最大计数

提问于
浏览 次
1

我有一个表 logins 与以下架构：

| id | user_id | weekday |
|----|---------|---------|
|  1 |       1 |       1 |
|  2 |       1 |       2 |
|  3 |       1 |       2 |

...

工作日是0到6之间的数字 .

我希望得到哪个 weekday 具有最高计数，对于表中的每个 user_id .

我尝试了以下查询：

SELECT MAX(num) as max_num, user_id, weekday
FROM (
    SELECT COUNT(*) as num, user_id, weekday
    FROM logins
    GROUP BY user_id, weekday
) C
WHERE user_id = C.user_id AND num = C.num
GROUP BY user_id;

这让我 weekday = 1 而不是 2 . 我认为我不应该在这里使用 WHERE 条款，但我无法获得正确的结果 .

我检查了其他类似的问题但没有运气，例如：

我用我的例子创建了一个SQL小提琴：http://sqlfiddle.com/#!9/e43a71/1

mysql sql greatest-n-per-group

2 回答

  • 1
    SELECT days.user_id, days.weekday, days.num
FROM (
    SELECT user_id, MAX(num) AS num
    FROM (
      SELECT user_id, weekday, COUNT(*) AS num
      FROM logins
      GROUP BY user_id, weekday
    ) max
    GROUP BY user_id
) nums
JOIN (
    SELECT user_id, weekday, COUNT(*) as num
    FROM logins
    GROUP BY user_id, weekday
) days ON(days.user_id = nums.user_id AND days.num = nums.num);

-- With Mariadb 10.2 or MySQL 8.0.2
WITH days AS (
    SELECT user_id, weekday, COUNT(*) as num
    FROM logins
    GROUP BY user_id, weekday
)
SELECT days.user_id, days.weekday, days.num
FROM (
    SELECT user_id, MAX(num) AS num
    FROM days
    GROUP BY user_id
) nums
JOIN days ON(days.user_id = nums.user_id AND days.num = nums.num);
    回复于
  • 0

    这是一个方法：

    SELECT user_id, MAX(num) as max_num, 
       SUBSTRING_INDEX(GROUP_CONCAT(weekday ORDER BY num DESC), ',', 1) as weekday_max
FROM (SELECT user_id, weekday, COUNT(*) as num
      FROM logins l
      GROUP BY user_id, weekday
     ) uw
GROUP BY user_id;
    回复于

相关问题