求大佬指点分析函数

[peruna]

上面是查询出的结果

下面是预期的结果，

每一列的a4 = a1+a2-a3

每一列的a1等于上一行计算出来的a4

不知有没有办法实现这个效果

[二月十六]

用递归的方式可以实现这个问题

[peruna]

引用 1 楼 sinat_28984567 的回复:

用递归的方式可以实现这个问题

你好，能提供一下思路吗，或者重点函数，我自己研究一下。

[二月十六]

如果日期是连续的直接可以用下边这个写法，如果不是，按照日期生成一列自增值，把datediff那里改一下：

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([a] Date,[a1] int,[a2] int,[a3] int,[a4] int)
Insert #T
select '2018-04-01',0,100,200,0 union all
select '2018-04-02',0,50,150,0 union all
select '2018-04-03',0,300,100,0
Go
--测试数据结束
;WITH cte AS (
SELECT a,
       a1,
       a2,
       a3,
       a1 + a2 - a3 AS a4
FROM #T
WHERE a = '2018-04-01'
UNION ALL
SELECT #T.a,
       cte.a4 AS a1,
       #T.a2,
       #T.a3,
       cte.a4 + #T.a2 - #T.a3
FROM #T
    JOIN cte
        ON DATEDIFF(DAY, cte.a, #T.a) = 1
)
SELECT * FROM cte

[peruna]

引用 3 楼 sinat_28984567 的回复:

如果日期是连续的直接可以用下边这个写法，如果不是，按照日期生成一列自增值，把datediff那里改一下：

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([a] Date,[a1] int,[a2] int,[a3] int,[a4] int)
Insert #T
select '2018-04-01',0,100,200,0 union all
select '2018-04-02',0,50,150,0 union all
select '2018-04-03',0,300,100,0
Go
--测试数据结束
;WITH cte AS (
SELECT a,
       a1,
       a2,
       a3,
       a1 + a2 - a3 AS a4
FROM #T
WHERE a = '2018-04-01'
UNION ALL
SELECT #T.a,
       cte.a4 AS a1,
       #T.a2,
       #T.a3,
       cte.a4 + #T.a2 - #T.a3
FROM #T
    JOIN cte
        ON DATEDIFF(DAY, cte.a, #T.a) = 1
)
SELECT * FROM cte

谢谢，大概思路我清楚了，日期不是连续的，我用row_number()试试。多谢！

[二月十六]

引用 4 楼 peruna 的回复:

[quote=引用 3 楼 sinat_28984567 的回复:] 如果日期是连续的直接可以用下边这个写法，如果不是，按照日期生成一列自增值，把datediff那里改一下：

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([a] Date,[a1] int,[a2] int,[a3] int,[a4] int)
Insert #T
select '2018-04-01',0,100,200,0 union all
select '2018-04-02',0,50,150,0 union all
select '2018-04-03',0,300,100,0
Go
--测试数据结束
;WITH cte AS (
SELECT a,
       a1,
       a2,
       a3,
       a1 + a2 - a3 AS a4
FROM #T
WHERE a = '2018-04-01'
UNION ALL
SELECT #T.a,
       cte.a4 AS a1,
       #T.a2,
       #T.a3,
       cte.a4 + #T.a2 - #T.a3
FROM #T
    JOIN cte
        ON DATEDIFF(DAY, cte.a, #T.a) = 1
)
SELECT * FROM cte

谢谢，大概思路我清楚了，日期不是连续的，我用row_number()试试。多谢！[/quote] 对，用rownumber生成自增，然后把链接条件cte.rn+1 = #T.rn 这样形式的

[RINK_1]

借用#3版主的数据

SELECT a,ISNULL(B.SUBTOTAL,0) AS a1,a2,a3,ISNULL(B.SUBTOTAL,0)+a2-a3 AS a4
FROM #T A
OUTER APPLY (SELECT SUM(a1+a2-a3) AS SUBTOTAL FROM #T WHERE a<A.a) AS B