按日期范围对数据进行分组 [英] Grouping data by date ranges

问题描述

我想知道如何根据日期范围选择数据范围吗?

I wonder how do I select a range of data depending on the date range?

我的付款表中有这些数据，格式为dd/mm/yyyy

I have these data in my payment table in format dd/mm/yyyy

 Id Date       Amount

 1   4/1/2011  300
 2  10/1/2011  200
 3  27/1/2011  100
 4   4/2/2011  300
 5  22/2/2011  400
 6   1/3/2011  500
 7   1/1/2012  600

截止日期为每月27日.因此我想将下个月27到26的所有数据归为一组.

The closing date is on the 27 of every month. so I would like to group all the data from 27 till 26 of next month into a group.

表示我想要这样的输出.

Meaning to say I would like the output as this.

 Group 1
 1      4/1/2011   300
 2     10/1/2011   200

 Group 2
 1     27/1/2011   100
 2      4/2/2011   300
 3     22/2/2011   400

 Group 3 
 1      1/3/2011   500

 Group 4 
 1      1/1/2012   600

推荐答案

目前尚不清楚您的问题的上下文.您要查询数据库吗?

It's not clear the context of your qestion. Are you querying a database?

在这种情况下，您正在询问日期时间，但似乎您有一个字符串格式的列.

If this is the case, you are asking about datetime but it seems you have a column in string format.

首先，将数据转换为datetime数据类型(或同等的数据，您正在使用什么数据库引擎?)，然后使用如下分组条件:

First of all, convert your data in datetime data type (or some equivalent, what db engine are you using?), and then use a grouping criteria like this:

GROUP BY datepart(month, dateadd(day, -26, [datefield])), DATEPART(year, dateadd(day, -26, [datefield]))

那么，您在Linq吗? 不同的语言，相同的逻辑:

So, you are in Linq? Different language, same logic:

.GroupBy(x => DateTime
    .ParseExact(x.Date, "dd/mm/yyyy", CultureInfo.InvariantCulture) //Supposed your date field of string data type
    .AddDays(-26)
    .ToString("yyyyMM"));

这篇关于按日期范围对数据进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！