tsql - TSQL - 循环，按条件对行求和

问题描述

在某些情况下，我将不胜感激。我有如下数据：

表和数据插入寻求帮助

CREATE TABLE [dbo].[TestOptions](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Crt_Day] [int] NOT NULL,
    [Lc_Day] [int] NOT NULL,
    [IsActive] [bit] NOT NULL,
    [Weight] [int] NOT NULL
) ON [PRIMARY]

INSERT INTO [dbo].[TestOptions](Crt_Day, Lc_Day, IsActive, [Weight]) VALUES(1,5,0,50);
INSERT INTO [dbo].[TestOptions](Crt_Day, Lc_Day, IsActive, [Weight]) VALUES(3,6,0,10);
INSERT INTO [dbo].[TestOptions](Crt_Day, Lc_Day, IsActive, [Weight]) VALUES(5,10,0,45);
INSERT INTO [dbo].[TestOptions](Crt_Day, Lc_Day, IsActive, [Weight]) VALUES(6,14,0,7);
INSERT INTO [dbo].[TestOptions](Crt_Day, Lc_Day, IsActive, [Weight]) VALUES(10,12,0,42);
INSERT INTO [dbo].[TestOptions](Crt_Day, Lc_Day, IsActive, [Weight]) VALUES(12,13,1,40);
INSERT INTO [dbo].[TestOptions](Crt_Day, Lc_Day, IsActive, [Weight]) VALUES(14,19,1,11);

我的问题是如何通过 CTE/光标或其他方式获得如下结果。我想要 crt_Day 中每个不同日期的总重量。但此值应包括基于条件的其他行的值：

如果其他行 Crt_Day < this.Crt_Day 和 Lc_Day < this.Crt_Day 则包括行权重
如果行 IsActive 不检查 Lc_Day，则应包括该行权重

我期望的最终结果+我的评论

Crt_Day     SumWeightForDay   

  1              50                f1st row was added
  3              60                50+10 2nd row was added , 1st has Lc_Day later
  5              55                10+45  ( 50 is to remove because of Lc_Day <=5 )
  6              52                7+45   we lost 10
  10             49                7+42    we lost 45
  12             47                7+40     we lost 42
  14             51                11+40   we lost 7 but 40 is Active so we dont check Lc_Day

为了更好地理解我在 C# 中的代码，它完全符合预期：

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

namespace ConsoleApp1
{
    class Program
    {
        class Detail
        {
            public int Id { get; set; }
            public int CrtDay { get; set; }
            public int LcDay { get; set; }
            public bool IsActive { get; set; }
            public int Weight { get; set; }
        }
        static void Main(string[] args)
        {

            var list = new List<Detail>
            {
                new Detail{Id =1, CrtDay = 1, LcDay = 5,IsActive = false, Weight = 50},
                new Detail{Id =2, CrtDay = 3, LcDay = 6,IsActive = false, Weight = 10},
                new Detail{Id =3, CrtDay = 5, LcDay = 10,IsActive = false, Weight = 45},
                new Detail{Id =4, CrtDay = 6, LcDay = 14,IsActive = false, Weight = 7},
                new Detail{Id =5, CrtDay = 10, LcDay = 12,IsActive = false, Weight = 42},
                new Detail{Id =6, CrtDay = 12, LcDay = 13,IsActive = true, Weight = 40},
                new Detail{Id =7, CrtDay = 14, LcDay = 19,IsActive = true, Weight = 11}
            };

            var days = list.Select(x => x.CrtDay).ToList();

            foreach (var day in days)
            {
                 var weight = list
                    .Where(x => (x.CrtDay <= day && x.LcDay > day && !x.IsActive)
                    || (x.CrtDay <= day && x.IsActive))
                    .Sum(x => x.Weight);

                  Console.WriteLine($"{day} {weight}");
            }
        }
    }
}

标签： tsql

SELECT t1.Crt_Day, SUM(t1.Weight + ISNULL(t2.Weight,0)) SumWeightForDay FROM dbo.TestOptions AS t1 LEFT JOIN dbo.TestOptions AS t2 ON t2.Crt_Day < t1.Crt_Day AND (t2.Lc_Day > t1.Crt_Day OR t2.IsActive = 1 ) GROUP BY t1.Crt_Day

tsql - TSQL - 循环，按条件对行求和

问题描述

解决方案

推荐阅读