34,590
社区成员
发帖
与我相关
我的任务
分享
如果ID不连续,用这个:
----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-05-26 21:55:20
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([Id] [int],[Flag] [nvarchar](10),[Amt] [float])
INSERT INTO [tb]
SELECT '1','A1','34' UNION ALL
SELECT '2','A2','45.2' UNION ALL
SELECT '3','A3','38' UNION ALL
SELECT '4','A4','83' UNION ALL
SELECT '5','A5','76.8' UNION ALL
SELECT '7','A6','23' UNION ALL
SELECT '9','A7','54' UNION ALL
SELECT '11','A8','11' UNION ALL
SELECT '12','A9','0.98' UNION ALL
SELECT '13','A10','23'
-->SQL查询如下:
IF OBJECT_ID('P_TEST') IS NOT NULL
DROP PROC P_TEST
GO
CREATE PROC P_TEST
@c1 int,
@c2 int
AS
SET NOCOUNT ON
DECLARE @S nvarchar(max),@S1 nvarchar(max),@I int
SET @I=1
WHILE @c1>=@I
BEGIN
SET @S=ISNULL(@S+'+','SELECT t1.Id,''(') +'''+t'+LTRIM(@I)+'.Flag+'''
SET @I=@I+1
END
SET @S=@S+')-('''
SET @I=1
WHILE @c2>=@I
BEGIN
SET @S=@S+'+t'+LTRIM(@I)+'.Flag+''+'''
SET @I=@I+1
END
SET @S=STUFF(@S,LEN(@S)-1,2,')''')+' [Desc],''('''
SET @I=1
WHILE @c1>=@I
BEGIN
SET @S=@S+'+LTRIM(t'+LTRIM(@I)+'.Amt)+''+'''
SET @I=@I+1
END
SET @S=STUFF(@S,LEN(@S)-2,2,''')-(')
SET @I=1
WHILE @c2>=@I
BEGIN
SET @S=@S+'+LTRIM(t'+LTRIM(@I)+'.Amt)+''+'''
SET @I=@I+1
END
SET @S=STUFF(@S,LEN(@S)-1,2,')''')+' [Amt1],Amt=CAST(0 AS Float)'
SET @I=2
WHILE @c1>=@I
BEGIN
SET @S1=ISNULL(@S1,' INTO # FROM [T] t1') +' JOIN [T] t'+LTRIM(@I)+' ON t'+LTRIM(@I-1)+'.ID=t'+LTRIM(@I)+'.ID-1'
SET @I=@I+1
END
SET @S1=@S1+'
DECLARE @S NVARCHAR(1000),@Id INT,@Amt Float
DECLARE C CURSOR FOR
SELECT ID,''SELECT @Amt=''+Amt1 FROM #
OPEN C
FETCH C INTO @Id,@S
WHILE @@FETCH_STATUS=0
BEGIN
EXEC SP_EXECUTESQL @S,N''@Amt Float OUT'',@Amt OUT
UPDATE # SET Amt=@Amt WHERE Id=@Id
FETCH C INTO @Id,@S
END
CLOSE C
DEALLOCATE C
SELECT ID,[Desc],Amt FROM #'
EXEC(';WITH T AS (SELECT ID=ROW_NUMBER()OVER(ORDER BY ID),[Flag],[Amt] FROM tb)'+ @S+@S1)
SET NOCOUNT OFF
GO
EXEC P_TEST 4,2
/*
ID Desc Amt
----------- --------------------------------------------------------------------- ----------------------
1 (A1+A2+A3+A4)-(A1+A2) 121
2 (A2+A3+A4+A5)-(A2+A3) 159.8
3 (A3+A4+A5+A6)-(A3+A4) 99.8
4 (A4+A5+A6+A7)-(A4+A5) 77
5 (A5+A6+A7+A8)-(A5+A6) 65
6 (A6+A7+A8+A9)-(A6+A7) 11.98
7 (A7+A8+A9+A10)-(A7+A8) 23.98
(7 行受影响)
*/
EXEC P_TEST 4,2
/*
ID Desc Amt
----------- --------------------------------------------------------------------- ----------------------
1 (A1+A2+A3+A4)-(A1+A2) 121
2 (A2+A3+A4+A5)-(A2+A3) 159.8
3 (A3+A4+A5+A6)-(A3+A4) 99.8
4 (A4+A5+A6+A7)-(A4+A5) 77
5 (A5+A6+A7+A8)-(A5+A6) 65
6 (A6+A7+A8+A9)-(A6+A7) 11.98
7 (A7+A8+A9+A10)-(A7+A8) 23.98
(7 行受影响)
*/
----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-05-26 21:55:20
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([Id] [int],[Flag] [nvarchar](10),[Amt] [float])
INSERT INTO [tb]
SELECT '1','A1','34' UNION ALL
SELECT '2','A2','45.2' UNION ALL
SELECT '3','A3','38' UNION ALL
SELECT '4','A4','83' UNION ALL
SELECT '5','A5','76.8' UNION ALL
SELECT '6','A6','23' UNION ALL
SELECT '7','A7','54' UNION ALL
SELECT '8','A8','11' UNION ALL
SELECT '9','A9','0.98' UNION ALL
SELECT '10','A10','23'
-->SQL查询如下:
IF OBJECT_ID('P_TEST') IS NOT NULL
DROP PROC P_TEST
GO
CREATE PROC P_TEST
@c1 int,
@c2 int
AS
SET NOCOUNT ON
DECLARE @S nvarchar(max),@S1 nvarchar(max),@I int
SET @I=1
WHILE @c1>=@I
BEGIN
SET @S=ISNULL(@S+'+','SELECT t1.Id,''(') +'''+t'+LTRIM(@I)+'.Flag+'''
SET @I=@I+1
END
SET @S=@S+')-('''
SET @I=1
WHILE @c2>=@I
BEGIN
SET @S=@S+'+t'+LTRIM(@I)+'.Flag+''+'''
SET @I=@I+1
END
SET @S=STUFF(@S,LEN(@S)-1,2,')''')+' [Desc],''('''
SET @I=1
WHILE @c1>=@I
BEGIN
SET @S=@S+'+LTRIM(t'+LTRIM(@I)+'.Amt)+''+'''
SET @I=@I+1
END
SET @S=STUFF(@S,LEN(@S)-2,2,''')-(')
SET @I=1
WHILE @c2>=@I
BEGIN
SET @S=@S+'+LTRIM(t'+LTRIM(@I)+'.Amt)+''+'''
SET @I=@I+1
END
SET @S=STUFF(@S,LEN(@S)-1,2,')''')+' [Amt1],Amt=CAST(0 AS Float)'
SET @I=2
WHILE @c1>=@I
BEGIN
SET @S1=ISNULL(@S1,' INTO # FROM [tb] t1') +' JOIN [tb] t'+LTRIM(@I)+' ON t'+LTRIM(@I-1)+'.ID=t'+LTRIM(@I)+'.ID-1'
SET @I=@I+1
END
SET @S1=@S1+'
DECLARE @S NVARCHAR(1000),@Id INT,@Amt Float
DECLARE C CURSOR FOR
SELECT ID,''SELECT @Amt=''+Amt1 FROM #
OPEN C
FETCH C INTO @Id,@S
WHILE @@FETCH_STATUS=0
BEGIN
EXEC SP_EXECUTESQL @S,N''@Amt Float OUT'',@Amt OUT
UPDATE # SET Amt=@Amt WHERE Id=@Id
FETCH C INTO @Id,@S
END
CLOSE C
DEALLOCATE C
SELECT ID,[Desc],Amt FROM #'
EXEC(@S+@S1)
SET NOCOUNT OFF
GO
EXEC P_TEST 5,2
/*
ID Desc Amt
----------- -------------------------------------------------------------------------------- ----------------------
1 (A1+A2+A3+A4+A5)-(A1+A2) 197.8
2 (A2+A3+A4+A5+A6)-(A2+A3) 182.8
3 (A3+A4+A5+A6+A7)-(A3+A4) 153.8
4 (A4+A5+A6+A7+A8)-(A4+A5) 88
5 (A5+A6+A7+A8+A9)-(A5+A6) 65.98
6 (A6+A7+A8+A9+A10)-(A6+A7) 34.98
(7 行受影响)
*/