按星期在日期对象中分组

本文关键字：对象 日期 | 更新日期: 2023-09-27 18:11:47

我正在用LINQ-to-SQL编写一个查询，在这个查询中是一个Date列。我想按"周"对结果进行分组。我如何在LINQ-to-SQL中做到这一点?

谢谢

按星期在日期对象中分组

假设公历为"第一天"周，且一周的第一天为星期一。

第一天周规则:

表示一年的第一周从月的第一天开始一年和结束前的下一个指定的第一天周

int firstDayOfWeek = (int)DayOfWeek.Monday;
var q = 
    from u in TblUsers
    let date = u.CreateDate.Value
    let num = date.DayOfYear - 1
    let num2 = ((int)date.DayOfWeek) - (num % 7)
    let num3 = ((num2 - firstDayOfWeek) + 14) % 7
    let week = (((num + num3) / 7) + 1)
    group u by week into g
    select new 
    {
        Week = g.Key,
        Count = g.Count ()
    };

希望对您有所帮助:

    var d1 = new DateTime(1990, 1, 1);
    var d2 = new DateTime(1990, 1, 2);
    var d3 = new DateTime(1990, 1, 11);
    var d4 = new DateTime(1990, 1, 12);
    var records = new[] { 
        new { date = d1, freq = 1 }, 
        new { date = d2, freq = 2 }, 
        new { date = d3, freq = 3 }, 
        new { date = d4, freq = 4 } 
    };
    //ticks * WeekDays * DayHours * HourMins * MinSecs
    var weekTicks = (Int64)10000000 * 7 * 24 * 60 * 60;   
    var res = records.GroupBy(x => x.date.Ticks / weekTicks)
             .Select(x => new { week=x.Key ,sum=x.Sum(y=>y.freq)});

结果如下:

[0] = {week = 99085, sum = 3}

[1] = {week = 99086, sum = 7}

相关文章：