的MySQL从一个子查询组

问题描述：

我有一个表logins下面的模式获取最大计数最高计数，表中每个user_id。的MySQL从一个子查询组

我尝试以下查询：

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

答

这是上午ethod：

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;

注意'COUNT（*）'可能并不总是完全用在InnoDB由于这只是现在固定在MySQL 19年7月5日的错误工作。我之前遇到过这个问题。似乎升级到5.7.19后工作正常。 https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-19.html –

@Marc。。。你真的更喜欢这个的其他答案？ –

答

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

如果您有Mariadb 10.2或MySQL 8.0.2+，则可以使用窗口函数或WITH statment编写更好和更短的查询。 –

的MySQL从一个子查询组

相关推荐