首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >mysql查询(根据活动拉取用户数)

问mysql查询(根据活动拉取用户数)
EN

Stack Overflow用户
提问于 2013-02-20 00:20:23
回答 2查看 68关注 0票数 1

我有2个MySQL表(用户和评论)-我想做的是获取一个报告,给我有多少用户发表了1条评论,有多少用户发表了2条评论,有多少用户发表了3条评论,有多少用户发表了4+评论,按月和年分组。

我使用这个查询来获取每个用户按年/月分组的评论数量

代码语言:javascript
复制
select year(c.datecreated) as comment_year, month(c.datecreated) as comment_month,      
count(c.id) as num_comments
from tblcomments c
inner join tbluser u on u.id = c.userid
where 
c.datecreated <= '2013-02-19' and c.datecreated >= '2012-03-01'
group by c.userid, year(c.datecreated), month(c.datecreated)

如何修改此查询以获得我想要的结果?

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2013-02-20 00:32:41

使用子查询第二次对结果进行分组:

代码语言:javascript
复制
SELECT   ym, 
         SUM(c = 1) AS `num_1`,
         SUM(c = 2) AS `num_2`,
         SUM(c = 3) AS `num_3`,
         SUM(c>= 4) AS `num_4+`
FROM (
  SELECT   DATE_FORMAT(datecreated, '%Y-%m') AS ym, COUNT(*) AS c
  FROM     tblcomments
  WHERE    datecreated BETWEEN '2012-03-01' AND '2013-02-19'
  GROUP BY ym, userid
) t
GROUP BY ym
票数 2
EN

Stack Overflow用户

发布于 2013-02-20 00:34:15

这里有一种方法--不确定您是否需要加入tbluser,但我把它留在那里:

代码语言:javascript
复制
SELECT comment_year, comment_month,
  COUNT(userId) userCnt,
  Num_Comments 
FROM (
  select 
    year(c.datecreated) as comment_year, 
    month(c.datecreated) as comment_month,      
    c.userid,
    CASE WHEN count(c.id) >= 4 THEN '4+' ELSE CAST(COUNT(c.id) as varchar) END as num_comments
  from tblcomments c
    inner join tbluser u on u.id = c.userid
  where 
    c.datecreated <= '2013-02-19' and c.datecreated >= '2012-03-01'
  group by c.userid, year(c.datecreated), month(c.datecreated)
  ) t
GROUP BY comment_year, comment_month, Num_Comments;

和一些示例小提琴:http://sqlfiddle.com/#!3/5fb5c/5

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/14962259

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档