首先，我只是無法在標題中正確解釋我的問題，但我認為它不是重復的。我有一個模型串列，它喜歡按多個欄位分組，然后將它們計入新欄位，并將所有其他欄位保留在結果中。我很抱歉這個糟糕的解釋，我已經研究了幾個小時如何做到這一點，但我無法弄清楚..我想從中得到：

按 SaleID 和 PrintType 分組，如下所示：

最好 id 喜歡使用 LINQ，但 id 對 SQL 也很好，id 只是想盡可能避免使用 for 回圈。編輯：有一定數量的列印型別，因此它不需要是動態的。

uj5u.com熱心網友回復：

為了聚合一定數量的 PrintType 的資料，您可以按 SaleId 分組，因為地址、電話和產品等附加資料對于 SaleId 是相同的（基于您的示例資料）。

var aggregated = from x in GenerateSales() 
    group x by x.SaleId into g // Assume that SaleId is the key that you want to group by
    select new Aggregate() 
    { 
        SaleId = g.Key, 
        // Additional data that are the same for all rows with the same SaleId
        Name = g.First().Name, 
        Product = g.First().Product, 
        Address = g.First().Address, 
        Phone = g.First().Phone, 
        // Calculate counts of Print Types
        NoOfPrintType1 = g.Where(x => x.PrintType == "PrintType1").Count(),
        NoOfPrintType2 = g.Where(x => x.PrintType == "PrintType2").Count(),
        NoOfPrintType3 = g.Where(x => x.PrintType == "PrintType3").Count(),
    };

Linq 陳述句首先按 SaleId 分組，然后為每個 SaleId 創建一個物件，其中包括

• 銷售 ID
• 地址、電話等附加資料...
• 每個已知 PrintType 的計數（通過過濾組的專案然后計算行數來計算）

您可以在下面找到生成測驗資料并輸出結果的示例。

結果

112 | Joe | Apple | New street 12 | 11223344 | 1 | 3 | 1
113 | Joe | Kiwi | New street 12 | 11223344 | 0 | 0 | 1
114 | Jane | Orange | New street 19 | 72754722 | 1 | 0 | 0
115 | John | Orange | New street 11 | 99236527 | 0 | 2 | 0

示例代碼

using System;
using System.Collections.Generic;
using System.Linq;

namespace LinqTest
{
    class Sale 
    {
        public string SaleId { get; set; }
        public string Name { get; set; }
        public string Product { get; set; }
        public string Address { get; set; }
        public string Phone { get; set; }
        public string PrintType { get; set; }
    }

    class Aggregate 
    {
        public string SaleId { get; set; }
        public string Name { get; set; }
        public string Product { get; set; }
        public string Address { get; set; }
        public string Phone { get; set; }
        public int NoOfPrintType1 { get; set; }
        public int NoOfPrintType2 { get; set; }
        public int NoOfPrintType3 { get; set; }

        public override string ToString() 
        {
            return $"{SaleId} | {Name} | {Product} | {Address} | {Phone} | {NoOfPrintType1} | {NoOfPrintType2} | {NoOfPrintType3}";
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var aggregated = from x in GenerateSales() 
                group x by x.SaleId into g // Assume that SaleId is the key that you want to group by
                select new Aggregate() 
                { 
                    SaleId = g.Key, 
                    // Additional data that are the same for all rows with the same SaleId
                    Name = g.First().Name, 
                    Product = g.First().Product, 
                    Address = g.First().Address, 
                    Phone = g.First().Phone, 
                    // Calculate counts of Print Types
                    NoOfPrintType1 = g.Where(x => x.PrintType == "PrintType1").Count(),
                    NoOfPrintType2 = g.Where(x => x.PrintType == "PrintType2").Count(),
                    NoOfPrintType3 = g.Where(x => x.PrintType == "PrintType3").Count(),
                };
            foreach(var a in aggregated)
            {
                Console.WriteLine(a.ToString());
            }
        }

        static IEnumerable<Sale> GenerateSales()
        {
            yield return new Sale() { SaleId = "112", Name = "Joe", Product = "Apple", Address = "New street 12", Phone = "11223344", PrintType = "PrintType1" };
            yield return new Sale() { SaleId = "112", Name = "Joe", Product = "Apple", Address = "New street 12", Phone = "11223344", PrintType = "PrintType2" };
            yield return new Sale() { SaleId = "112", Name = "Joe", Product = "Apple", Address = "New street 12", Phone = "11223344", PrintType = "PrintType2" };
            yield return new Sale() { SaleId = "112", Name = "Joe", Product = "Apple", Address = "New street 12", Phone = "11223344", PrintType = "PrintType2" };
            yield return new Sale() { SaleId = "112", Name = "Joe", Product = "Apple", Address = "New street 12", Phone = "11223344", PrintType = "PrintType3" };
            yield return new Sale() { SaleId = "113", Name = "Joe", Product = "Kiwi", Address = "New street 12", Phone = "11223344", PrintType = "PrintType3" };
            yield return new Sale() { SaleId = "114", Name = "Jane", Product = "Orange", Address = "New street 19", Phone = "72754722", PrintType = "PrintType1" };
            yield return new Sale() { SaleId = "115", Name = "John", Product = "Orange", Address = "New street 11", Phone = "99236527", PrintType = "PrintType2" };
            yield return new Sale() { SaleId = "115", Name = "John", Product = "Orange", Address = "New street 11", Phone = "99236527", PrintType = "PrintType2" };
        }
    }
}

uj5u.com熱心網友回復：

您可以使用此 SQL 查詢

with x as (
   SELECT  [SaleID],[Name],[Product],[Address],[Phone],[PrintType]
          ,case when [PrintType]='PrintType1' then count(*) else 0 end NoOfPrintType1
          ,case when [PrintType]='PrintType2' then count(*) else 0 end NoOfPrintType2
          ,case when [PrintType]='PrintType3' then count(*) else 0 end NoOfPrintType3
      FROM [Table_1]
      group by [SaleID] ,[Name],[Product],[Address],[Phone] ,[PrintType])
    
    select [SaleID],[Name],[Product],[Address],[Phone]
          ,sum(NoOfPrintType1)NoOfPrintType1
          ,sum(NoOfPrintType2)NoOfPrintType2
          ,sum(NoOfPrintType3)NoOfPrintType3 from x
   group by [SaleID]
          ,[Name],[Product],[Address],[Phone]

標籤：C＃ 林克

上一篇：C#linqjoin回傳多個結果

下一篇：C#LINQ從串列中選擇，其中值不包含在資料庫中