如何在SQLServer/C#Linq中按分组、计算和选择小于给定金额的位置

本文关键字：小于 选择 计算 位置 金额 SQLServer C#Linq | 更新日期: 2023-09-27 18:25:34

如何按年份分组并计算总和，选择SQL Server或C#中TotalCollected小于给定金额的位置。

我想按Year和Refno对数据进行分组，计算总和并选择总和结果小于-10000的地方。

我当前的代码在SQL Server 中是这样的

SELECT 
    Year, Refno, SetaCode,
    SUM(TotalCollected) AS TotalCollected
FROM 
    transactions 
GROUP BY 
    Year, Refno, TypeCode  
ORDER BY
    Year, Refno, TypeCode;

电流输出如下：

   | Year | Refno     | TypeCode  | TotalCollected |
   +------+-----------+-----------+----------------+
   | 2015 | G30714630 | 16        | -534713,04     |
   | 2015 | G30738332 | 16        | -16471         |
   | 2015 | G00746893 | 11        | -441770,75     |
   | 2015 | G10712596 | 11        | -1254,53       |
   | 2015 | G30711221 | 21        | -740616,41     |
   | 2015 | G50769901 | 21        | -1131          |
   | 2014 | G30714630 | 16        | -26575,4       |
   | 2014 | G10703033 | 11        | -122156,62     |
   | 2013 | G00750516 | 21        | -31578,38      |
   | 2013 | G30775855 | 21        | -9032,19       |
   | 2013 | G50749525 | 21        | 0              |
   | 2013 | G30714630 | 16        | -0,4           |
   | 2013 | G10703033 | 11        | 527,17         |
   +------+-----------+-----------+----------------+

任何帮助，请即使你可以用C#查询代码回复它也很好。

请帮帮我，伙计们，我被卡住了。

如何在SQLServer/C#Linq中按分组、计算和选择小于给定金额的位置

Linq版本：

var result = transactions
    .GroupBy(t => new { t.Year, t.Refno, t.SetaCode })
    .Select(grp => new
    {
        Year = grp.Key.Year,
        Refno = grp.Key.Refno,
        SetaCode = grp.Key.SetaCode,
        TotalCollected = grp.Sum(x => x.TotalCollected)
    })
    .Where(r => r.TotalCollected < -100000)
    .OrderBy(r => r.Year).ThenBy(r => r.Refno).ThenBy(r => r.SetaCode)
    .ToList();
相关文章：