Linq to SQL选择不同和求和 [英] Linq to SQL select distinct and sum

问题描述

我有一个表格，其中保存有关产品质量控制的每日统计信息.我需要每周生成有关哪些产品通过/未通过质量控制的报告.

I have a table that holds daily statistics on product QC. I need to produce a weekly report on which products passed/failed QC.

我可以使用以下方式获取数据:

I can get the data using:

Dim productsPassed = (From p In dc.tblProductStats
                      Where p.StatDate >= startDate And p.StatDate <= endDate And p.Status = "PASS"
                      Order By p.Product)

因为这是每日统计信息的列表，所以我可以使用p.Product和p.PCount列获取以下信息

As this is a list of daily stats I get the following by using p.Product and p.PCount columns

ASD 1 ASD 1 ASD 4 FGG 1 FGG 5 头盔显示器1 JWC 1 JWC 3 ....等

ASD 1 ASD 1 ASD 4 FGG 1 FGG 5 HMD 1 JWC 1 JWC 3 .... etc

我需要这样: ASD 6 FGG 6 头盔显示器1 JWC 4 ....

I need this to be : ASD 6 FGG 6 HMD 1 JWC 4 ....

我尝试使用Select p.Product Distinct，但无法弄清楚如何对p.PCount进行计数

I've tried using Select p.Product Distinct but can't figure out how to sum up the count of p.PCount

任何帮助表示赞赏.

更新

基于Ric的代码，我尝试进行转换，以便仍然有一个列表，以后我会在productsPassed中使用For Each产品，并使用StringBuilder建立html表.

Based on Ric's code I have tried to convert so that I still have a list which I later use a For Each product In productsPassed and build up a html table using StringBuilder.

我现在拥有的代码是

Dim ProductsPassed = (From p In dc.tblProductStats
                              Where (Function(p) p.StatDate >= ReportStartDate AndAlso p.StatDate <= ReportEndDate AndAlso p.PStatus = "PASS")
                              Group By (Function(p) p.Product)
                              Select (Function(p) New With {.Product = p.Key,
                                                            .PCount = p.Sum(Function(item) item.PCount)}))

最初我自己拥有 GROUP ，而VS则抱怨它期望 BY ，所以我添加了它，现在它抱怨它期望有 INTO .另外，我不确定项目的来源.

Originally I had GROUP on its own and VS complained it expected BY so I added that and now it complains it expects an INTO. Also I'm not sure where item came from.

推荐答案

尝试一下(未经测试，并可能对您的结构造成危险的猜测):

Try this (untested and hazarding a guess at your structure):

Dim ProductsPassed = dc.tblProductStats.   
    Where(Function(p) p.StatDate >= startDate AndAlso
                   p.StatDate <= endDate AndAlso p.Status = "PASS").
    GroupBy(Function(p) p.Product).
    Select(Function(p) New With {
        .Product = p.Key,
        .Count = p.Sum(Function(item) item.PCount)
    })

使用我组成的一些示例类，将产生以下输出

Using some sample classes I made up produces the following output

这篇关于Linq to SQL选择不同和求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！