TSQL - 比较具有自联接的两列 - 计算昨天总计和今天总计之间的差异

Question

我试图计算一列上今天和昨天的总数之间的差异。如果自我加入不是最好的方法，那也没关系，无论哪一个能给我带来我想要的结果都应该没问题。

要求：

1. 只比较最近两天的数据，即使表中有多天的数据量，但每天只有一个条目
2. 计算该列昨天的总数和今天的总数之间的差值

问题

下面的代码返回一个零，我不明白为什么。

为什么不计算，我该怎么做才能满足要求

---

IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1
CREATE TABLE #t1 (
countID UNIQUEIDENTIFIER
,empCount VARCHAR(20)
,CountDate DATETIME
)
INSERT INTO #t1 (
countID
, empCount
, CountDate
)
VALUES
(NEWID(),'123000', GETDATE()) 
,(NEWID(),'100', '20200813')
,(NEWID(),'100', '20200810')
SELECT 
today.countID
, (CAST(today.empCount AS INT)) - (CAST(yesterday.empCount AS INT)) AS CountDiff
, today.empCount
, today.CountDate
FROM #t1 AS today
INNER JOIN #t1 AS yesterday ON today.countID = yesterday.countID
AND yesterday.CountDate > (SELECT dateadd(day,datediff(day,2,GETDATE()),0))

Answer 1

我想你想要lag():

select t.*,
(empcount - lag(empcount) over (order by countdate)) as diff
from #t1 t;

如果你只想要最后两天，那么：

select top (1) t.*
from (select t.*,
(empcount - lag(empcount) over (order by countdate)) as diff
from #t1 t
) t
order by countdate desc;

注意：这解释了"；昨天"；作为表中的最后两天。如果你真的想要今天和昨天，那么你可以使用where子句：

select top (1) t.*
from (select t.*,
(empcount - lag(empcount) over (order by countdate)) as diff
from #t1 t
where countdate >= dateadd(day, -1, convert(date, getdate()))
) t
order by countdate desc;