这个SQL有几种写法？

[jimoshatan]

CREATE TABLE #A (S_NO VARCHAR(10),AMT INT)
INSERT INTO #A SELECT '001',20
INSERT INTO #A SELECT '002',30
INSERT INTO #A SELECT '003',20 
INSERT INTO #A SELECT '004',40
INSERT INTO #A SELECT '005',25 
INSERT INTO #A SELECT '006',35 
INSERT INTO #A SELECT '007',60


DROP TABLE #A
/*TOT_AMT为当前记录与前面纪录的和，按照S_NO排序
S_NO    AMT   TOT_AMT
001	20     20
002	30     50
003	20     70
004	40     110
005	25     135
006	35     170
007	60     230

[liangCK]

CREATE TABLE #A (S_NO VARCHAR(10),AMT INT)
INSERT INTO #A SELECT '001',20
INSERT INTO #A SELECT '002',30
INSERT INTO #A SELECT '003',20 
INSERT INTO #A SELECT '004',40
INSERT INTO #A SELECT '005',25 
INSERT INTO #A SELECT '006',35 
INSERT INTO #A SELECT '007',60

SELECT *
FROM #A AS a
 CROSS APPLY
 (
   SELECT SUM(AMT) SAMT
   FROM #A
   WHERE S_NO<=a.S_NO
 ) AS b

DROP TABLE #A


/*
S_NO       AMT         SAMT
---------- ----------- -----------
001        20          20
002        30          50
003        20          70
004        40          110
005        25          135
006        35          170
007        60          230

(7 行受影响)
*/

[liangCK]

CREATE TABLE #A (S_NO VARCHAR(10),AMT INT)
INSERT INTO #A SELECT '001',20
INSERT INTO #A SELECT '002',30
INSERT INTO #A SELECT '003',20 
INSERT INTO #A SELECT '004',40
INSERT INTO #A SELECT '005',25 
INSERT INTO #A SELECT '006',35 
INSERT INTO #A SELECT '007',60

SELECT *,
       SAMT=(SELECT SUM(AMT)
             FROM #A
             WHERE S_NO<=a.S_NO)
FROM #A AS a


DROP TABLE #A


/*
S_NO       AMT         SAMT
---------- ----------- -----------
001        20          20
002        30          50
003        20          70
004        40          110
005        25          135
006        35          170
007        60          230

(7 行受影响)

*/

[$扫地僧$]

select T.S_NO,T.AMT,
(select sum(AMT) from #A where S_NO<=T.S_NO) as TOT_AMT
from #A T

[liangCK]

CREATE TABLE #A (S_NO VARCHAR(10),AMT INT)
INSERT INTO #A SELECT '001',20
INSERT INTO #A SELECT '002',30
INSERT INTO #A SELECT '003',20 
INSERT INTO #A SELECT '004',40
INSERT INTO #A SELECT '005',25 
INSERT INTO #A SELECT '006',35 
INSERT INTO #A SELECT '007',60

SELECT a.S_NO,a.AMT,SUM(b.AMT) SAMT
FROM #A AS a
 LEFT OUTER JOIN #A AS b
ON a.S_NO>=b.S_NO
GROUP BY a.S_NO,a.AMT

DROP TABLE #A


/*
S_NO       AMT         SAMT
---------- ----------- -----------
001        20          20
003        20          70
005        25          135
002        30          50
006        35          170
004        40          110
007        60          230

(7 行受影响)

*/

[水族杰纶]

SELECT *,
       SAMT=(SELECT SUM(AMT)
             FROM #A
             WHERE S_NO<=a.S_NO)
FROM #A AS a

[fan22176391]

强！学习。

[super_iven]

学习