CREATE TABLE #T(D int)
INSERT INTO #T
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 12 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 3
SELECT *,IDENTITY(int,1,1) ID INTO #A FROM #T
SELECT A.ID,SUM(B.D) AS 结果 FROM #A A LEFT OUTER JOIN #A B ON B.ID BETWEEN A.ID AND A.ID+2
WHERE A.ID%3=1
GROUP BY A.ID
ORDER BY A.ID
/*
ID 结果
----------------
1 6
4 17
7 17
10 11
*/
DROP TABLE #T ,#A
INSERT INTO #T
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 12 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 3
SELECT *,IDENTITY(int,1,1) ID INTO #A FROM #T
SELECT A.ID,SUM(B.D) AS 结果 FROM #A A LEFT OUTER JOIN #A B ON B.ID BETWEEN A.ID AND A.ID+2
WHERE A.ID%3=1
GROUP BY A.ID
ORDER BY A.ID
/*
ID 结果
----------------
1 6
4 17
7 17
10 11
*/
DROP TABLE #T ,#A
上次也碰到这样的一个问题,可以查看帖子:http://www.cnblogs.com/wghao/archive/2007/11/09/954192.html
大致的方法是差不多的,只是写法不一样。
不知道还有没有可以不用临时表和IDENTITY()函数来实现的方法?
要是哪位大虾知道,能否拿出来分享?谢谢了。