SQL 填充缺少的日期条目，并包括上一个日期的计数

Question

我有一个表如下

<表类> 日期 Id 组 名称 ScoreCount tbody><<tr>2022-06-201运动员亚当522022-06-231运动员亚当772022-06-251运动员亚当792022-06-191员工亚当652022-06-221员工亚当28

您可以简单地尝试颠倒连接表的顺序-

WITH t as (SELECT Id,
Group,
Name,
min(Date) as MinDate,
max(Date) as MaxDate
FROM recordTable
GROUP BY Id,Group,Name
)
SELECT t.Id,
t.Group,
t.Name,
c.Days,
(SELECT LAST_VALUE(ScoreCount) OVER(<your over clause is missing>)
FROM recordTable 
WHERE t.Id = recordTable.Id
AND t.Group = recordTable.Group)
FROM calendar c
LEFT JOIN t ON c.Days BETWEEN t.MinDate AND t.MaxDate

虽然我还没有测试这个查询，但这将给你一个进一步进行的想法。

您不需要last_value，您可以获得第一个值

WITH t as (
SELECT
[Id],
[Group],
[Name],
min([Date]) as MinDate,
max([Date]) as MaxDate
FROM recordTable
GROUP BY [Id],[Group],[Name]
)
SELECT 
t.Id,
t.[Group],
t.[Name],
c.[Date],
(SELECT top 1 ScoreCount 
from recordTable x 
where x.[Date] <= c.[Days]
and  x.[Group] = t.[Group] 
and x.[Name] = t.[Name]
order by x.[Date] desc
) ScoreCount
FROM t
LEFT JOIN calendar c ON c.[Days] BETWEEN t.MinDate AND t.MaxDate