时间戳

首页 > 解决方案 > MySQL group by 仅针对特定数量的记录（在 JOIN 表达式中查询）

mysql - MySQL group by 仅针对特定数量的记录（在 JOIN 表达式中查询）

问题描述

我有一个历史表，它代表某种聊天程序存储的消息。

它有一个用户 ID、消息和日期时间。（指定于：http ://sqlfiddle.com/#!9/49fcefb/5 ）

现在，我想要的结果包含：

我让它工作了，但我发现查询相当慢，我认为这是因为最后一个 GROUP BY (GROUP BY b.cnt) 因为这将对找到的所有记录进行分组。

询问：

SELECT b.cnt as dayCount, a.cnt as userCount, a.userid as userId, b.date
 FROM (
        select date_added as date, user_id as userid, count(*) as cnt from history group by userid, day(date_added),month(date_added),year(date_added)
      ) a
 INNER JOIN (
        select date_added as date, count(*) as cnt from history group by day(date_added),month(date_added),year(date_added)
      ) b ON year(a.date) = year(b.date)
          AND month(a.date) = month(b.date)
          AND day(a.date) = day(b.date)
GROUP BY b.cnt
ORDER BY dayCount desc, userCount desc limit 10;

有人可以建议我如何解决这个问题吗？也许有另一种查询？

提前致谢！

标签： mysql

解决方案

似乎你加入了错误的标准。

第一个表正在获取用户数，第二个表正在获取每个 user_id 的天数。见dbfiddle

SELECT b.cnt as dayCount, a.cnt as userCount, a.userid as userId, b.date
FROM (
         select 
             user_id as userid
           , count(*) as cnt 
         from history 
         group by userid
      ) a
LEFT JOIN (
        select cast(date_added as date) as date
            , count(*) as cnt 
            , user_id as userid
        from history 
        group by cast(date_added as date)  
      ) b ON a.userid = b.userid
ORDER BY dayCount desc, userCount desc limit 10;

推荐阅读