LINQ to SQL将具有不同行的多列分组 [英] LINQ to SQL grouping multiple columns with a distinct row

问题描述

我具有以下表格结构.我想选择不同的CustomerId和CustomerName，TotalCost.

I have the following table structure. I want to select distinct CustomerId and CustomerName, TotalCost.

这是表结构和列数据类型.

Here's the table structure and column data type.

LogId (int)
CustomerId (string)
CustomerName (string)
Cost (int)

Logid/CustomerId/CustomerName/Cost

Logid / CustomerId / CustomerName / Cost

• 1 2031年John Steward 20
• 2 2035玛丽·乔10
• 3 2034罗伯特·塔克30
• 4 2031 John Setward 12
• 5 2036路加·大卫15
• 6 2033凯文·勒14
• 7 2035玛丽·乔9
• 8 2036路克·大卫8
• 9 2035玛丽·乔18
• 10 2037杰西·汤姆25
• 11 2032安东尼·詹姆斯27
• 12 2033凯文·勒26
• 1 2031 John Steward 20
• 2 2035 Mary Joe 10
• 3 2034 Robert Tuck 30
• 4 2031 John Setward 12
• 5 2036 Luke David 15
• 6 2033 Kevin Le 14
• 7 2035 Mary Joe 9
• 8 2036 Luke David 8
• 9 2035 Mary Joe 18
• 10 2037 Jesse Tom 25
• 11 2032 Antony James 27
• 12 2033 Kevin Le 26

更新1

到目前为止，这是我尝试进行的查询:

Here's my attempted query so far:

Dim db As New DemoDataContext()

    Dim query = From log In db.LogRecords _
                 Where log.Cost> 10 _
                 Group log By New With {log.CustomerId, log.CustomerName} Into g() _
                 Select New With {g.CustomerId, g.CustomerName, .Cost = g.Sum(Function(log) log.Cost)}

但这会产生错误消息只能从不带参数的简单名称或限定名称中推断出范围变量名称.

更新2

Dim queryResult = (From log In db.LogRecords _ 
    Group log By log.CustomerId, log.CustomerName Into Cost = Sum(log => log.Cost ) _ 
    Select New With { CustomerId, CustomerName, TotalCost })

For Each q In queryResult

Next

错误:未声明名称'queryResult'.

推荐答案

如果我正确理解了您的要求，则在C#中应该可以执行以下操作:

If I understand your requirements correctly, something like this should work in C#:

var query = from row in dataTable
            group row by new { row.CustomerId, row.CustomerName } into g
            select new
            {
                g.Key.CustomerId,
                g.Key.CustomerName,
                Cost = g.Sum(row => row.Cost)
            };

我想我最初对为什么它不起作用的想法是错误的.我们只是语法错误.

I guess my initial thought on why it didn't work was wrong. We just had wrong syntax.

Dim query = From log In db.LogRecords                     _
            Group log By log.CustomerId, log.CustomerName _
                Into Cost = Sum(log => log.Cost)          _
            Select CustomerId, CustomerName, Cost

这篇关于LINQ to SQL将具有不同行的多列分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！