前N筆平均值‏

以下SQL做法可以取出當筆資料前N筆(不含本筆資料)的平均值

當然 SUM(CALAMT)/COUNT(*)可以用AVG(CALAMT)取代

SELECT (SELECT SUM(CALAMT)/COUNT(*),* FROM (

(SELECT TOP 2 * FROM TABLE_NAME WHERE CUNO = T.CUNO AND DATE < T.DATE ORDER BY DATE DESC )

) AS A) AS AVGAMT

FROM TABLE_NAME T

WHERE 1=1