基于条件的 SQL 求某一行的总和？

Question

我正在使用 MS SQL Server。这是我拥有的表格：

Create table tblVal
(
Id int identity(1,1),
Val NVARCHAR(100),
startdate datetime,
enddate datetime
)
--Inserting Records--
Insert into tblVal values(500,'20180907','20191212')
Insert into tblVal values(720,'20190407','20191212')
Insert into tblVal values(539,'20190708','20201212')
Insert into tblVal values(341,'20190221','20190712')

以下是表格：

   Id   |Val        |startdate      |enddate
   --- ----------------------------------------------
    1   |500        |2018-09-07     |2019-12-12 
    2   |720        |2019-04-07     |2019-12-12 
    3   |539        |2019-07-08     |2020-12-12 
    4   |341        |2019-02-21     |2019-07-12    

我需要的是：

Mon     |   Total
------------------
Jan     |   500
Feb     |   841
March   |   841
April   |   1561 
May     |   1561
June    |   1561
July    |   2100
........|.........

我希望能在特定的月份内汇总Val列。例如，在四月这个月，它位于两行之间。我必须检查开始日期和结束日期的两个条件，然后再求和这些数值。

以下是我的尝试:

select *
into #ControlTable 
from dbo.tblVal

DECLARE @cnt INT = 0;
while @cnt<12
begin
select sum(CASE  
             WHEN MONTH(startdate) BETWEEN @cnt and MONTH(enddate) THEN 0 
              ELSE 0 
           END) 
 from #ControlTable
SET @cnt = @cnt + 1;
end

drop table #ControlTable

但是从上面的操作我无法达到想要的结果。我该如何解决这个问题？谢谢。

- Dungeon

---

我本来要打出解决方案，但我认为你也需要考虑年份，除非你想在总数中累加2018年4月和2017年4月（例如）的计数？您可以对月份进行求和，按年份和月份进行分组。 - Brad

@Brad 是的，我也需要年份。那样会更好。 - Dungeon

Answer 1

我相信你想要的是这样的：

with dates as (
      select min(datefromparts(year(startdate), month(startdate), 1)) as dte,
             max(datefromparts(year(enddate), month(enddate), 1)) as enddte
      from tblVal
      union all
      select dateadd(month, 1, dte), enddte
      from dates
      where dte < enddte
     )
select d.dte, sum(val)
from dates d left join
     tblval t
     on t.startdate <= eomonth(dte) and
        t.enddate >= dte
group by d.dte
order by d.dte;

这将计算数据中所有月份。

结果与您的示例结果有些不同，但似乎更符合提供的数据。

此处 是一个 db<>fiddle。

- Gordon Linoff

Answer 2

你好，如果我理解你的墙查询，我认为这个查询可以回答：

Create table #tblVal
(
Id int identity(1,1),
Val NVARCHAR(100),
startdate datetime,
enddate datetime
)
--Inserting Records--
Insert into #tblVal values(500,'20180907','20191212')
Insert into #tblVal values(720,'20190407','20191212')
Insert into #tblVal values(539,'20190708','20201212')
Insert into #tblVal values(341,'20190221','20190712')

Create table #tblMonth ( iMonth int)
Insert into #tblMonth values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);


select * from #tblVal
select * from #tblMonth

SELECT *, SUM(case when Val is null then 0 else cast (Val as int) end) OVER(ORDER BY iMonth
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as 'Totaltime'
FROM #tblMonth 
LEFT JOIN #tblVal ON MONTH(startdate) = iMonth
ORDER BY iMonth

drop table #tblVal
drop table #tblMonth

您需要使用SQL Server 2008版本或更高版本才能使用OVER(ORDER BY iMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)语句。

链接：

• https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017

如果您使用的是旧版本，则可以使用CTE或JOIN ON select。

- Sanpas

Answer 3

DECLARE @outpuTable Table(
MOn INT,
Total nvarchar(MAX)
)

DECLARE @cnt INT = 1;
while (@cnt<=12)
begin

INSERT INTo @outpuTable VALUES(@cnt,
(select ISNULL(sum(CONVERT(INT,Val)),0)
 from tblVal
WHERE  @cnt BETWEEN MONTH(startdate) and MONTH(enddate) ))

SET @cnt = @cnt + 1;
end

Select * from @outpuTable

- Hemang A