我有下表：
ID val DateTime
1 20 2018-12-11 14:57:20.620
2 30 2018-12-11 20:57:20.940
3 15 2018-12-12 00:57:20.460
4 41 2018-12-12 04:57:20.830
5 22 2018-12-12 11:57:20.077
另外，我有以下存储过程：
ALTER PROCEDURE [dbo].[C_PS_PS]
-- Add the parameters for the stored procedure here
@DataStart datetime=null,
@DataStop datetime=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT [ID]
,[val]
,[DateTime]
FROM [DB].[dbo].[C_Tbl]
WHERE DateTime between @DataStart and @DataStop
END
execute [C_PS_PS] '2018-12-11 14:57:20.620','2018-12-12 11:57:20.077'
当我执行它时，我得到了上表。我的问题是：我怎么能得到的差异val第一和最后一行之间的不同的DataStart和DataStop？
为了举例说明我想要什么，假设我想用 date2018-12-11 20:57:20.940和2018-12-12 04:57:20.830.
你可以做这样的事情。cte带来最旧的日期及其val，cte2带来最新的日期及其val。然后最终使用cross join并减去cte.val从cte2.val（或反之亦然）：
;with cte as
(
select top 1 [DateTime], val from [dbo].[C_Tbl]
WHERE [DateTime] between @DataStart and @DataStop
order by [DateTime] asc
),
cte2 as
(
select top 1 [DateTime], val from [dbo].[C_Tbl]
WHERE [DateTime] between @DataStart and @DataStop
order by [DateTime] desc
)
select
cte.[DateTime], cte2.[DateTime], cte.val, cte2.val,
--DATEDIFF( dd , cte.[DateTime], cte2.[DateTime]) 'difference_days'
cte.val-cte2.val
from cte cross join cte2
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句