又一表内公式计算应用实例

需求贴:http://topic.csdn.net/u/20100526/19/3aaa2ee0-7bb4-4c96-a6dd-a5811b2056d7.html?33002

 

现有一表Test如下
Id Flag Amt(float型)
1 A1 34
2 A2 45.2
3 A3 38
4 A4 83
5 A5 76.8
6 A6 23
7 A7 54
8 A8 11
9 A9 0.98
10 A10 23


现要求创建一个存储过程,向其中传入两个int变量而得到如下结果
(当传入4和2,两个值时,得到如下结果,4表示从这个Id号开始后四个依次的Id的Flag字段连接起来,
  2表示从这个Id号开始后2个依次的Id的Flag字段连接起来,然后相减,Amt字段也是一样)
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
当Id字段后面没有传入的值4那么多记录后,着不进行运算,比如上表中只有到ID为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 '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 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


(6 行受影响)
*/

阅读更多
想对作者说点什么? 我来说一句

宽幅输入电源中的应力

2010年07月22日 403KB 下载

sQL基础教程和应用实例

2009年04月06日 3.96MB 下载

WebService应用实例及教程

2012年03月10日 48KB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭