Mysql查询获取最近6个月按月分组的帖子

发布于 2024-10-12 11:56:08 字数 962 浏览 3 评论 0原文

我正在尝试使用 Google Charts API 创建用户在过去 6 个月内的帖子数量图表。我的表结构是这样的 -

-> Userid - varchar(20)
-> Post_year - varchar(4)
-> Post_month - varchar(2)

为了获取用户过去 6 个月的帖子数量，我有这样的查询...

SELECT COUNT(*) AS count, `Post_year`, `Post_month`
FROM (`table`)
WHERE `Userid` = '1234'
AND `Post_year` >= '$year_offset'
AND `Post_month` >= '$month_offset'
GROUP BY `Post_year`, `Post_month`

如您所见，我只存储帖子年份和月份，然后使用偏移量变量来获取帖子。这就像...

案例 1：如果当前年份和月份是 2010/12，则偏移量是 2010/7 情况 2：如果当前年份和月份是 2011/1，则偏移量是 2010/8

现在情况 1 中的查询按预期工作正常。

// Get all posts by a user
// ...
WHERE Post_year >= 2010
AND Post_month >= 7

但对于案例 2，则不然。由于查询的最后部分变成...

// Get all posts by a user
// ...
WHERE Post_year >= 2010
AND Post_month >= 8

因此，2010/1 的帖子不满足条件 Post_month >= 8。

我应该如何更改我的查询以使其在案例 2 中也能工作。

问候

原文

I am trying to create graph for the number of posts by a user in last 6 months using Google Charts API.
My table structure is like this -

-> Userid - varchar(20)
-> Post_year - varchar(4)
-> Post_month - varchar(2)

To get the number of posts in last 6 months by a user, I have my query like this...

SELECT COUNT(*) AS count, `Post_year`, `Post_month`
FROM (`table`)
WHERE `Userid` = '1234'
AND `Post_year` >= '$year_offset'
AND `Post_month` >= '$month_offset'
GROUP BY `Post_year`, `Post_month`

As you can see, I am storing just the post year and month, and then using an offset variable to get the posts. This works like...

Case 1: if current year and month are 2010/12, then offset ones are 2010/7
Case 2: if current year and month are 2011/1, then offset ones are 2010/8

Now the query in Case 1 works fine as it is supposed to.

// Get all posts by a user
// ...
WHERE Post_year >= 2010
AND Post_month >= 7

But in case of Case 2, it doesn't. Since the last part of query becomes...

// Get all posts by a user
// ...
WHERE Post_year >= 2010
AND Post_month >= 8

Hence, the posts of 2010/1 do not satisfy the condition Post_month >= 8.

How should I change my query for it to work in Case 2 as well.

Regards

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浅唱ヾ落雨殇 2024-10-19 11:56:08

您可以执行类似的操作

AND `Post_year` * 100 + `Post_month` >= $year_offset * 100 + $month_offset

，但正确的方法是引入 Post_date 字段并在查询中使用它。

You could do something like

AND `Post_year` * 100 + `Post_month` >= $year_offset * 100 + $month_offset

But proper way is to introduce Post_date field and use it in query.

回复收藏 0 原文

执笔绘流年 2024-10-19 11:56:08

SELECT 
  COUNT(*) AS count, 
  `Post_year`, 
  `Post_month`
FROM (`table`)
WHERE `Userid` = '1234'
  AND `Post_year` >= '$year_offset'
  AND `Post_month` >= '$month_offset'
UNION ALL
SELECT 
  COUNT(*) AS count, 
  `Post_year`, 
  `Post_month`
FROM (`table`)
WHERE `Userid` = '1234'
  AND `Post_year` = YEAR(CURRENT_TIMESTAMP)
  AND `Post_month` <= '$month_offset'
GROUP BY `Post_year`, `Post_month`

SELECT 
  COUNT(*) AS count, 
  `Post_year`, 
  `Post_month`
FROM (`table`)
WHERE `Userid` = '1234'
  AND `Post_year` >= '$year_offset'
  AND `Post_month` >= '$month_offset'
UNION ALL
SELECT 
  COUNT(*) AS count, 
  `Post_year`, 
  `Post_month`
FROM (`table`)
WHERE `Userid` = '1234'
  AND `Post_year` = YEAR(CURRENT_TIMESTAMP)
  AND `Post_month` <= '$month_offset'
GROUP BY `Post_year`, `Post_month`

回复收藏 0 原文

~没有更多了~