MySQL组之间的日期 [英] MySQL Group By Dates Between

问题描述

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

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

  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 
  

我试图编写一个查询，它将返回 2月 - 8月和<$ c $之间的记录数c> 9月 - 1月按年计算，所以我得到的结果是：

  2005年7月 - 2006年1月：3 
 2006年2月 -  2006年8月：5 
  

解决方案

你可以对任何你想要的东西进行分组。如果您可以在列中显示某些内容以显示您想要的内容，则可以对其进行分组。因此，只需两个时间段，您就可以简单地将其组合在一个if中：

  SELECT * FROM表
 GROUP BY if在2005-07-01和2006-02-01之间，0,1）
  

如果您需要更长的时间段（多年），您可以使用period_diff来区分：

  SELECT * FROM表
 GROUP BY floor（period_diff（DATE_FORMAT（stamp，％Y％m），200507）/ 6）
  

它会给出您的日期和年月格式的开始期间之间传递的6个月的块数。

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

解决方案

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.

这篇关于MySQL组之间的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！