需要一些groupby Linq2Sql帮助，请 [英] Need some groupby Linq2Sql help, please

问题描述

我需要显示我们按天，周，月和年出售的所有Products的历史记录.这些数据将被发送到Google Charts API，以显示结果的折线图.

I need to display a history all our Products we've sold by day, week, month and year. This data will be sent to google charts API to display a line graph of the results.

因此，如果我有一个名为Products的表，它看起来像:-

So if i have a table called Products and it looks like:-

ProductID INT
DateCreated DATETIMEOFFSET

，用户要求查看ProductID 1的历史记录.我该如何检索? 例如输出.

and the user asks to see the history for ProductID 1. How could i retrieve this? eg output.

Monday 1st Dec: 0
Tuesday 2nd Dec: 3
Wed 3rd Dec: 1

图2(周数与销售量)

Week 49 2008: 21
Week 50 2008: 11
Week 51 2008: 45
Week 52 2008: 0

Graph3(月数与销售量)

Dec 08: 102
Jan 09: 23

我不确定'by day'是否可以完成...或其中的任何一项.

I'm not sure if the 'by day' can be done ... or any of it.

欢呼:)

花了一些时间后，我得到了第一个工作的信息...但是仍然需要其他两个信息的帮助，并将其全部作为一个查询的一部分...

After spending a bit of time, i got the first one working... but still need help on the other two AND making it all part of one query...

from p in Products
where p.ProductId == 69
group p.DateCreated by p.DateCreated.Date into grouping
orderby grouping.Key
select new { Date = grouping.Key, Count = grouping.Count() }

推荐答案

var data = from p in ctx.Products
           where p.ProductID == *productId*
           group p by p.DateCreated.DayOfWeek into groupedProducts
           select new { DayOfWeek = groupedProducts.Key, Count = groupedProcuts.Count() };

未经测试，我认为这可能为您做到

Without testing I think that may do it for you

每年要执行以下操作:

var data = from p in ctx.Products
           where p.ProductID == *productId*
           group p by n.CreateDate.Year into gn
           select new {
            Count = from a in gn
                 group a by a.CreateDate.DayOfYear into aa
                 select new { Count = aa.Count(), Key = new DateTime(gn.Key , 1, 1).AddDays(aa.Key) }
           };

(对不起，变量名:P)

(Sorry about the variable names :P)

这篇关于需要一些groupby Linq2Sql帮助，请的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！