SQL计算的优化，求教大佬们，我尽力了

[honghuangbu0446]

描述都在图片里，自己写的SQL遇到数据量大的都要查个几分钟，95%的时间都在这计算上了，
还请大家帮帮忙，出谋划策下，
数据库是 SQL server 2008 R2

[吉普赛的歌]

贴下原始数据的文本吧。人家帮你， 首先就得模拟你的场景

[二月十六]

计算量大的可以加一列，存储计算结果，第一次初始化一次快慢无所谓，后期有变化就改变这一列，不要每次读取每次计算，很慢。

[二月十六]

另外楼主可以给出测试数据，对应结果，和自己写的语句，看看是不是还可以优化。

[Hello World,]

生成临时表，按材料ID、日期或者其他条件排好序，用变量（一个是材料ID，一个是上期结存数量）更新，不要用子查询

[Dear SQL(燊)]

1。采用开窗数函数，第n行是前n行的和，再用初始值+这个和 2。with 循环

[weixin_42334999]

2种做法，1个是递归

WITH t AS (
SELECT 1 AS rowid,377 AS id,0 AS IN_qty, 0 AS out,201 AS INIT_qty UNION ALL
SELECT 2 AS rowid,376 AS id,0 AS IN_qty, 0 AS out,312 AS IN_qtyit UNION ALL
SELECT 3 AS rowid,376 AS id,0 AS IN_qty, 16 AS out,0 AS IN_qtyit UNION ALL
SELECT 4 AS rowid,375 AS id,0 AS IN_qty, 0 AS out,-25 AS IN_qtyit UNION ALL
SELECT 5 AS rowid,298 AS id,0 AS IN_qty, 0 AS out,-12 AS IN_qtyit UNION ALL
SELECT 6 AS rowid,302 AS id,0 AS IN_qty, 0 AS out,-40 AS IN_qtyit UNION ALL
SELECT 7 AS rowid,303 AS id,0 AS IN_qty, 0 AS out,205 AS IN_qtyit UNION ALL
SELECT 8 AS rowid,305 AS id,0 AS IN_qty, 0 AS out,-48 AS IN_qtyit UNION ALL
SELECT 9 AS rowid,307 AS id,0 AS IN_qty, 0 AS out,-11 AS IN_qtyit UNION ALL
SELECT 10 AS rowid,308 AS id,0 AS IN_qty, 0 AS out,-7 AS IN_qtyit UNION ALL
SELECT 11 AS rowid,309 AS id,0 AS IN_qty, 0 AS out,987 AS IN_qtyit UNION ALL
SELECT 12 AS rowid,309 AS id,0 AS IN_qty, 2 AS out,0 AS IN_qtyit UNION ALL
SELECT 13 AS rowid,309 AS id,0 AS IN_qty, 11 AS out,0 AS IN_qtyit UNION ALL
SELECT 14 AS rowid,309 AS id,0 AS IN_qty, 4 AS out,0 AS IN_qtyit UNION ALL
SELECT 15 AS rowid,312 AS id,0 AS IN_qty, 0 AS out,-1 AS IN_qtyit UNION ALL
SELECT 16 AS rowid,316 AS id,0 AS IN_qty, 0 AS out,7898 AS IN_qtyit UNION ALL
SELECT 17 AS rowid,316 AS id,0 AS IN_qty, 98 AS out,0 AS IN_qtyit UNION ALL
SELECT 18 AS rowid,316 AS id,0 AS IN_qty, 164 AS out,0 AS IN_qtyit UNION ALL
SELECT 19 AS rowid,316 AS id,0 AS IN_qty, 280 AS out,0 AS IN_qtyit UNION ALL
SELECT 20 AS rowid,316 AS id,0 AS IN_qty, 167 AS out,0 AS IN_qtyit UNION ALL
SELECT 21 AS rowid,316 AS id,0 AS IN_qty, 6 AS out,0 AS IN_qtyit UNION ALL
SELECT 22 AS rowid,316 AS id,0 AS IN_qty, 108 AS out,0 AS IN_qtyit UNION ALL
SELECT 23 AS rowid,316 AS id,0 AS IN_qty, 84 AS out,0 AS IN_qtyit UNION ALL
SELECT 24 AS rowid,316 AS id,637 AS IN_qty, 0 AS out,0 AS IN_qtyit UNION ALL
SELECT 25 AS rowid,316 AS id,182 AS IN_qty, 0 AS out,0 AS IN_qtyit UNION ALL
SELECT 26 AS rowid,316 AS id,0 AS IN_qty, 9 AS out,0 AS IN_qtyit 
)
,t1 AS (
SELECT *,rnkid=ROW_NUMBER()OVER(partition BY id ORDER BY rowid ASC)  FROM t
)
,t2 AS (
SELECT *,t1.INIT_qty+in_qty-out AS balance FROM t1
WHERE rnkid=1
UNION ALL
SELECT t1.*,t2.balance+t1.INIT_qty+t1.in_qty-t1.out FROM t2 ,t1 
WHERE t2.id=t1.id
AND t2.rnkid+1=t1.rnkid
)
SELECT rowid,id,in_qty,out,init_qty,balance FROM t2
ORDER BY 1

第二个是计算对于每一行计算所有id相同 rowid比他小的行的3列值

WITH t AS (
SELECT 1 AS rowid,377 AS id,0 AS IN_qty, 0 AS out,201 AS INIT_qty UNION ALL
SELECT 2 AS rowid,376 AS id,0 AS IN_qty, 0 AS out,312 AS IN_qtyit UNION ALL
SELECT 3 AS rowid,376 AS id,0 AS IN_qty, 16 AS out,0 AS IN_qtyit UNION ALL
SELECT 4 AS rowid,375 AS id,0 AS IN_qty, 0 AS out,-25 AS IN_qtyit UNION ALL
SELECT 5 AS rowid,298 AS id,0 AS IN_qty, 0 AS out,-12 AS IN_qtyit UNION ALL
SELECT 6 AS rowid,302 AS id,0 AS IN_qty, 0 AS out,-40 AS IN_qtyit UNION ALL
SELECT 7 AS rowid,303 AS id,0 AS IN_qty, 0 AS out,205 AS IN_qtyit UNION ALL
SELECT 8 AS rowid,305 AS id,0 AS IN_qty, 0 AS out,-48 AS IN_qtyit UNION ALL
SELECT 9 AS rowid,307 AS id,0 AS IN_qty, 0 AS out,-11 AS IN_qtyit UNION ALL
SELECT 10 AS rowid,308 AS id,0 AS IN_qty, 0 AS out,-7 AS IN_qtyit UNION ALL
SELECT 11 AS rowid,309 AS id,0 AS IN_qty, 0 AS out,987 AS IN_qtyit UNION ALL
SELECT 12 AS rowid,309 AS id,0 AS IN_qty, 2 AS out,0 AS IN_qtyit UNION ALL
SELECT 13 AS rowid,309 AS id,0 AS IN_qty, 11 AS out,0 AS IN_qtyit UNION ALL
SELECT 14 AS rowid,309 AS id,0 AS IN_qty, 4 AS out,0 AS IN_qtyit UNION ALL
SELECT 15 AS rowid,312 AS id,0 AS IN_qty, 0 AS out,-1 AS IN_qtyit UNION ALL
SELECT 16 AS rowid,316 AS id,0 AS IN_qty, 0 AS out,7898 AS IN_qtyit UNION ALL
SELECT 17 AS rowid,316 AS id,0 AS IN_qty, 98 AS out,0 AS IN_qtyit UNION ALL
SELECT 18 AS rowid,316 AS id,0 AS IN_qty, 164 AS out,0 AS IN_qtyit UNION ALL
SELECT 19 AS rowid,316 AS id,0 AS IN_qty, 280 AS out,0 AS IN_qtyit UNION ALL
SELECT 20 AS rowid,316 AS id,0 AS IN_qty, 167 AS out,0 AS IN_qtyit UNION ALL
SELECT 21 AS rowid,316 AS id,0 AS IN_qty, 6 AS out,0 AS IN_qtyit UNION ALL
SELECT 22 AS rowid,316 AS id,0 AS IN_qty, 108 AS out,0 AS IN_qtyit UNION ALL
SELECT 23 AS rowid,316 AS id,0 AS IN_qty, 84 AS out,0 AS IN_qtyit UNION ALL
SELECT 24 AS rowid,316 AS id,637 AS IN_qty, 0 AS out,0 AS IN_qtyit UNION ALL
SELECT 25 AS rowid,316 AS id,182 AS IN_qty, 0 AS out,0 AS IN_qtyit UNION ALL
SELECT 26 AS rowid,316 AS id,0 AS IN_qty, 9 AS out,0 AS IN_qtyit 
)
SELECT *,
(SELECT SUM(init_qty+in_qty-out) FROM t b
WHERE a.id=b.ID
AND b.rowid<=a.rowid) AS balance FROM t a