表A
字段
id(int) 编号
name(varchar) 名称
quantity(int) 数量 ---- 这里的quantity会根据相同的name值进行累加,如果第一行记录为1,第二行记录会是1+输入数据
datetime 添加时间现在想查出 "特定时间区间里的一个数量quantity,如果在特定时间区间没有找到记录,则找出小于时间区间中起始时间的最后一条记录,然后根据名称汇总"想了好长时间还是没有头绪
字段
id(int) 编号
name(varchar) 名称
quantity(int) 数量 ---- 这里的quantity会根据相同的name值进行累加,如果第一行记录为1,第二行记录会是1+输入数据
datetime 添加时间现在想查出 "特定时间区间里的一个数量quantity,如果在特定时间区间没有找到记录,则找出小于时间区间中起始时间的最后一条记录,然后根据名称汇总"想了好长时间还是没有头绪
SELECT DISTINCT NAME FROM A)
SELECT ISNULL(A1.ID,A2.ID) AS ID
,ISNULL(A1.NAME,A2.NAME) AS NAME
,ISNULL(A1.quantity,A2.quantity) AS quantity
,ISNULL(A1.[datetime] ,A2,[datetime]) AS [datetime]
FROM CTE C LEFT JOIN A A1
ON A1.NAME = C.NAME
AND A1.[datetime] BETWEEN @StartTime AND @EndTime
LEFT JOIN A A2
ON A2.NAME = C.NAME AND A1.NAME IS NULL
AND A2.[datetime] < @StartTime
AND NOT EXISTS (
SELECT 1 FROM A
WHERE NAME = A2.NAME
AND [datetime] < @StartTime
AND [datetime] > A2.[datetime]
)
WHERE ISNULL(A1.NAME,A2.NAME) IS NOT NULL
select * from tb where dt between begintime and endtime
union all
select * from(
select top 1 * from tb where not exists(select 1 from tb where dt between begintime and endtime)
and dt<begintime
order by dt desc
)t)t1
select * from tb where dt between begintime and endtime
union all
select * from(
select top 1 * from tb where not exists(select 1 from tb where dt between begintime and endtime)
and dt<begintime
group by 名称
order by dt desc
)t)t1
谢谢 qianjin036a 兄