求解决问题

[Iverson_MVP_3]

表 test
ID store
1 33
2 55
3 66
4 88
5 12

查询后要得到这个结果
ID store store1 store2
1 33 33 33
2 55 88 121
3 66 154 275
4 88 242 517
5 12 254 771

[freecodex]

算法： Row(i)Column(j) = Row(i)Column(j-1) + Row(i-1)Column(j) , i >= 2 && j >= 2

[还在加载中灬]

;WITH CTE AS(
	SELECT T1.ID,T1.store
		,SUM(T2.store)store1
	FROM test T1
		JOIN test T2 ON T1.ID>=T2.ID
	GROUP BY T1.ID,T1.store
)
SELECT T1.ID,T1.store,T1.store1
	,SUM(T2.store1)store2
FROM CTE T1
	JOIN CTE T2 ON T1.ID>=T2.ID
GROUP BY T1.ID,T1.store,T1.store1
ORDER BY T1.ID

[Tiger_Zhao]

WITH /* 测试数据
test(ID,store) AS (
    SELECT 1,33 UNION ALL
    SELECT 2,55 UNION ALL
    SELECT 3,66 UNION ALL
    SELECT 4,88 UNION ALL
    SELECT 5,12 
), */
t1(ID,store1) AS (
    SELECT * FROM test WHERE id=1
    UNION ALL
    SELECT t0.id,
           t0.store+t1.store1
      FROM t1
      JOIN test t0
        ON t0.id = t1.id+1
),
t2(ID,store2) AS (
    SELECT * FROM test WHERE id=1
    UNION ALL
    SELECT t1.id,
           t1.store1+t2.store2
      FROM t2
      JOIN t1
        ON t1.id = t2.id+1
)
SELECT t0.id,
       t0.store,
       t1.store1,
       t2.store2
  FROM test t0
  JOIN t1
    ON t0.id = t1.id
  JOIN t2
    ON t0.id = t2.id

         id       store      store1      store2
----------- ----------- ----------- -----------
          1          33          33          33
          2          55          88         121
          3          66         154         275
          4          88         242         517
          5          12         254         771

[spiritofdragon]

WITH t(ID,STORE) AS (

SELECT 1,	  33	
UNION ALL SELECT 2,	55	 
UNION ALL SELECT 3,	66	 
UNION ALL SELECT 4,	  88	 
UNION ALL SELECT 5,	  12	 
)
, cte as (
select *,STORE STORE1
from t t1 
where t1.ID=1
union all
select t2.*,t2.STORE+cte.STORE1
from t t2 
join cte on t2.ID=cte.ID+1
)
, cte2 as (
select *,STORE STORE2
from cte 
where ID=1
union all
select t2.*,t2.STORE1+cte2.STORE2
from cte t2
join cte2 on t2.ID=cte2.ID+1
)
select * from cte2

[还在加载中灬]

SELECT T1.ID,T1.store
	,SUM(DISTINCT T2.store)store1
	,SUM(T3.store)store2
FROM test T1
	JOIN test T2 ON T1.ID>=T2.ID
	JOIN test T3 ON T2.ID>=T3.ID
GROUP BY T1.ID,T1.store
ORDER BY T1.ID