代码之家 › 专栏 › 技术社区 › Murali Dhara Sudhan P

如何根据每天的最大日期时间值筛选数据[已关闭]

linq-to-entities datetime linq c#

-4

Murali Dhara Sudhan P · 技术社区 · 12 年前

前任:

 ID Name Age Salary ClicksAsofToday  Timestamp

 1. Arun  25 25000    5               Aug,20 10.50pm
 2. Arun  25 2000     8               Aug,20 10.55pm
 3. Arun  25 25000    13              Aug,20 11.00pm
 4. Vijay 25 20000    2               Aug,20 10.50pm
 5. Vijay 25 20000    3               Aug,20 10.55pm
 6. Vijay 25 20000    8               Aug,20 11.00pm
 7. Vijay 25 20000    3               Aug,21 10.55pm
 8. Vijay 25 20000    8               Aug,21 12.00pm

我希望结果是

 ID Name Age Salary  ClicksAsofToday  Timestamp

 1. Arun  25 25000    13              Aug,20 11.00pm (Max time of that Particular date)
 2. Vijay 25 20000    8               Aug,20 11.00pm
 8. Vijay 25 20000    8               Aug,21 12.00pm (Max time of another date)

时间戳列的类型为 DateTime

1 回复 | 直到 12 年前

Eugene Podskal 12 年前

使用以下数据:

public class Data
{
    public String Name
    {
        get;
        set;
    }

    public DateTime TimeStamp
    {
        get;
        set;
    }

    public Data(String name, DateTime timeStamp)
    {
        this.Name = name;
        this.TimeStamp = timeStamp;
    }

    public override string ToString()
    {
        return String.Format("{0} at {1}", this.Name, this.TimeStamp);
    }
}

您可以使用:

var source = new Data[]
{
    new Data("Yesterday early", DateTime.Today.AddDays(-1)),
    new Data("Yesterday middle", DateTime.Today.AddDays(-1).AddHours(2)),
    new Data("Yesterday middle", DateTime.Today.AddDays(-1).AddHours(2)),
    new Data("Yesterday late", DateTime.Today.AddDays(-1).AddHours(3)),

    new Data("Today early", DateTime.Today),
    new Data("Today middle", DateTime.Today.AddHours(2)),
    new Data("Today late 1", DateTime.Today.AddHours(3)),
    new Data("Today late 2", DateTime.Today.AddHours(3)),

    new Data("Tomorrow early", DateTime.Today.AddDays(1)),
    new Data("Tomorrow middle", DateTime.Today.AddDays(1).AddHours(2)),
    new Data("Tomorrow late 1", DateTime.Today.AddDays(1).AddHours(3)),
    new Data("Tomorrow late 2", DateTime.Today.AddDays(1).AddHours(3)),
};


var lateInDays = source.
    GroupBy((data) => data.TimeStamp.Date).
    Select(dayGroup =>
        new 
        {
            DayGroup = dayGroup, 
            MaxTimeInDay = dayGroup.Max(data => data.TimeStamp)
        }).
    SelectMany((dayGroupWithMaxTime) =>
        dayGroupWithMaxTime.
            DayGroup.
            Where(data =>
                (data.TimeStamp == dayGroupWithMaxTime.MaxTimeInDay)));

这远不是最优的,但它是有效的。

工作原理:

1. GroupBy((data) => data.TimeStamp.Date). -按日期分组数据项(三天有三组)

2. 零件:

 Select(dayGroup =>
        new 
        {
            DayGroup = dayGroup, 
            MaxTimeInDay = dayGroup.Max(data => data.TimeStamp)
        }).

用关于一天中最长时间的信息(实际上是完整的DateTime,因此您可能希望使用DateTime.TimeOfDay属性而不是完整的DateTime)来扩充三个按天分组中的每个分组

3. 部分

 SelectMany((dayGroupWithMaxTime) =>
        dayGroupWithMaxTime.
            DayGroup.
            Where(data =>
                (data.TimeStamp == dayGroupWithMaxTime.MaxTimeInDay)));

从每个组(每天)中选择具有最大时间的所有记录,只需将每个记录的时间戳与最大时间(对于当前组(天))进行比较即可:

 dayGroupWithMaxTime.
     DayGroup.
     Where(data =>
               (data.TimeStamp == dayGroupWithMaxTime.MaxTimeInDay))