MySQL 按日期进行分组

发布于 2024-12-15 18:50:23 字数 681 浏览 2 评论 0原文

有没有办法对两个日期之间的记录进行分组？

例如，我的表中的记录如下所示：

rid     stamp                   uid
25      2005-07-05 14:10:29     25
1175    2005-08-12 15:47:35     29
290     2005-11-22 16:38:53     42
30      2005-12-01 10:48:12     47
30      2006-01-02 17:34:28     52
30      2006-02-06 22:11:35     57
30      2006-04-17 15:10:19     59
1195    2006-05-08 21:55:56     62
100     2006-06-30 15:51:04     94
45      2006-07-03 21:14:37     24

我正在尝试编写一个查询，该查询将返回 February - August 和 September - January< 之间的记录计数/code> 按年，所以我得到的是：

July 2005 - January 2006:      3
February 2006 - August 2006:   5

原文

Is there a way to group records that fall between two dates?

For example, my table has records that look like this:

rid     stamp                   uid
25      2005-07-05 14:10:29     25
1175    2005-08-12 15:47:35     29
290     2005-11-22 16:38:53     42
30      2005-12-01 10:48:12     47
30      2006-01-02 17:34:28     52
30      2006-02-06 22:11:35     57
30      2006-04-17 15:10:19     59
1195    2006-05-08 21:55:56     62
100     2006-06-30 15:51:04     94
45      2006-07-03 21:14:37     24

I'm trying to write a query that will return a count of records between the months of February - August and between September - January by year, so that what I get back is:

July 2005 - January 2006:      3
February 2006 - August 2006:   5

分享到QQ

分享到微博

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

发布评论

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

舂唻埖巳落 2024-12-22 18:50:23

您几乎可以对任何您想要的事情进行分组。如果您可以在列中获取某些内容来显示您想要的内容，则可以对其进行分组。因此，对于两个周期，您可以简单地按 if 进行分组：

SELECT * FROM table
GROUP BY if(stamp between "2005-07-01" and "2006-02-01", 0, 1)

如果您需要周期范围更长（多年），您可以使用 period_diff 来区分：

SELECT * FROM table
GROUP BY floor(period_diff( DATE_FORMAT(stamp, "%Y%m"), "200507") / 6)

它将为您提供日期之间经过的 6 个月块的数量以及年月格式的开始期间。

You can group on -almost- anything you want to. If you can get something in a column to show what you want, you can group on it. So for just two periods you could simply group on an if:

SELECT * FROM table
GROUP BY if(stamp between "2005-07-01" and "2006-02-01", 0, 1)

If you need the periods to range over longer times (multiple years), you could use period_diff to distinguish:

SELECT * FROM table
GROUP BY floor(period_diff( DATE_FORMAT(stamp, "%Y%m"), "200507") / 6)

It will give you the number of 6-month blocks passed between your date and the start-period in year-month format.

回复收藏 0 原文

自在安然 2024-12-22 18:50:23

稍微结合其他...
您可以根据您想要的特定范围扩展案例结构。 group by 遵循“序数”列 1 的位置，因此您不必在 group by 子句中复制相同的情况条件。此外，我还添加了“排序依据”以具有相同的案例上下文，因为否则它会按字母顺序对结果进行排序。您可以按您想要的任何顺序进行排序...如果您希望首先列出最新的日期活动，甚至可以反向排序。

SELECT
      case when stamp between "2005-07-01" and "2006-02-01"
              then "July 2005 - January 2006   "
           when stamp between "2006-02-01" and "2006-08-01"
              then "February 2006 - August 2006"
           else    "After August 2006          "
      end as GroupColumn,
      count(*) as TotalPerGroup
   from  
      table
   where 
      date >= '2005-07-01'
   group by 
      1
   order by 
      case when stamp between "2005-07-01" and "2006-02-01"
              then 1
           when stamp between "2006-02-01" and "2006-08-01"
              then 2
           else    3
      end

In slight combination of the others...
you can expand the case structure for however range specific you want. The group by respects the "ordinal" column 1 position so you don't have to copy the same case condition in the group by clause. Additionally, I threw in the Order by to have same context of the case since it would otherwise order the results alphabetically. You can order by whatever you wanted... even in reverse if you wanted most current date activity listed first.

SELECT
      case when stamp between "2005-07-01" and "2006-02-01"
              then "July 2005 - January 2006   "
           when stamp between "2006-02-01" and "2006-08-01"
              then "February 2006 - August 2006"
           else    "After August 2006          "
      end as GroupColumn,
      count(*) as TotalPerGroup
   from  
      table
   where 
      date >= '2005-07-01'
   group by 
      1
   order by 
      case when stamp between "2005-07-01" and "2006-02-01"
              then 1
           when stamp between "2006-02-01" and "2006-08-01"
              then 2
           else    3
      end

回复收藏 0 原文

同展鸳鸯锦 2024-12-22 18:50:23

如果只有一组，那么您可以使用“group by withhaving 子句”。我认为没有语法可以在一个语句中指定多个不同的分组并将它们分开。这是我能想到的最简单的替代解决方案：

select "July 2005 - January 2006" AS "Date", count(date) as "results" 
from MYTABLE 
where date >= '2005-07-01' AND date <= '2006-01-31'

union

select "February 2006 - August 2006" AS "Date", count(date) as "results" 
from MYTABLE 
where date >= '2006-02-01' AND date <= '2006-08-31';

If there were only one group then you could use the "group by with having clause". I don't think there is syntax to specify multiple different groupings in one statement and separate them though. Here is the simplest alternative solution I can think of that will work:

select "July 2005 - January 2006" AS "Date", count(date) as "results" 
from MYTABLE 
where date >= '2005-07-01' AND date <= '2006-01-31'

union

select "February 2006 - August 2006" AS "Date", count(date) as "results" 
from MYTABLE 
where date >= '2006-02-01' AND date <= '2006-08-31';

回复收藏 0 原文

~没有更多了~