如何按多列分组统计数量并保留其余字段，优先用LINQ或SQL实现

LINQ 实现 #

假设原始数据的实体类为SaleRecord，原始数据集合为saleRecords，直接按SaleID分组后取公共字段、分类统计计数即可：

var result = saleRecords
    // 按SaleID分组，同SaleID的其余公共字段完全一致
    .GroupBy(s => s.SaleID)
    .Select(g => new 
    {
        SaleID = g.Key,
        Name = g.First().Name,
        Product = g.First().Product,
        Address = g.First().Address,
        Phone = g.First().Phone,
        NoOfPrintType1 = g.Count(x => x.PrintType == "PrintType1"),
        NoOfPrintType2 = g.Count(x => x.PrintType == "PrintType2"),
        NoOfPrintType3 = g.Count(x => x.PrintType == "PrintType3")
    })
    .ToList();

如果需要强类型结果，可以自定义输出实体类替换匿名类即可。

SQL 实现 #

使用条件聚合语法即可实现，假设原始表名为sale_records：

SELECT 
    SaleID,
    Name,
    Product,
    Address,
    Phone,
    SUM(CASE WHEN PrintType = 'PrintType1' THEN 1 ELSE 0 END) AS NoOfPrintType1,
    SUM(CASE WHEN PrintType = 'PrintType2' THEN 1 ELSE 0 END) AS NoOfPrintType2,
    SUM(CASE WHEN PrintType = 'PrintType3' THEN 1 ELSE 0 END) AS NoOfPrintType3
FROM sale_records
GROUP BY SaleID, Name, Product, Address, Phone

内容的提问来源于stack exchange，提问作者user16402126