SQL 合同订单有多个版本,行后加列求版本差额(3与2比较 2与1比较,总是高版本与低版本比较)

[2017_PX]

请教各位大牛, 最后一列为版本号,totalAmount为合同金额，我想要的格式为, 每行后面加一列为 高版本与低版本比较后的差, 例子：3 版(100金额)与 2版(50金额) 比较为50
需要考虑,有的订单只有1个版本，有的为1-10版本不等 ,需要用到什么函数呢或者什么逻辑? 谢谢

[二月十六]

建议楼主列出表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。 参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382

[2017_PX]

您好！, 现在我要求出合同订单中多个版本的差额, 一个合同会有多个订单修改后的版本,例如 CS170407这个合同有三个修改后的版本,为1,2,3 计算方式为(TotalAmount金额与上个版本进行比较，3与2比较,2与1比较,1版本为0不用与上版本比较) 源数据 Initialcontract TotalAmount Version No CS170407 1200 1 CS170407 1400 2 CS170407 1800 3 结果 Initialcontract TotalAmount Version No 加列(SumAmount) CS170407 1200 1 0 版本1 不进行比较 为0 CS170407 1400 2 200 (2版本的TotalAmount与1版本的TotalAmount 比较) 1400 - 1200 = 200 CS170407 1800 3 400 (3版本的TotalAmount与2版本的TotalAmount 比较) 1800- 1400 = 400

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([Initialcontract] nvarchar(28),[TotalAmount] int,[VersionNo] int)
Insert #T
select N'CS170407',1200,1 union all
select N'CS170407',1400,2 union all
select N'CS170407',1800,3
Go
--测试数据结束
;WITH ctea AS (
SELECT  * ,
        ROW_NUMBER() OVER ( PARTITION BY Initialcontract ORDER BY VersionNo ) AS rn
FROM    #T
),cteb AS (
SELECT  * ,
        0 AS SumAmount
FROM    ctea
WHERE   rn = 1
UNION ALL
SELECT  ctea.* ,
        ctea.TotalAmount - cteb.TotalAmount
FROM    ctea
        JOIN cteb ON cteb.rn + 1 = ctea.rn
                     AND cteb.Initialcontract = ctea.Initialcontract
)
SELECT  Initialcontract ,
        TotalAmount ,
        VersionNo ,
        SumAmount
FROM    cteb

[2017_PX]

Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column "SumAmount" of recursive query "cteb".

你好,我的SumAmount为Decimal（18,2）,我把“0 AS SumAmount” 改成 CAST('0' AS decimal(18,2)) AS SumAmount 或者 0.00 都还是报上面错
非常感谢你的帮助！

[2017_PX]

谢谢, 可以了

[二月十六]

;WITH ctea AS (
SELECT  * ,
        ROW_NUMBER() OVER ( PARTITION BY Initialcontract ORDER BY VersionNo ) AS rn
FROM    #T
),cteb AS (
SELECT  * ,
        CONVERT(DECIMAL(18,2),0.00) AS SumAmount
FROM    ctea
WHERE   rn = 1
UNION ALL
SELECT  ctea.* ,
         CONVERT(DECIMAL(18,2),ctea.TotalAmount - cteb.TotalAmount)
FROM    ctea
        JOIN cteb ON cteb.rn + 1 = ctea.rn
                     AND cteb.Initialcontract = ctea.Initialcontract
)
SELECT  Initialcontract ,
        TotalAmount ,
        VersionNo ,
        SumAmount
FROM    cteb

[2017_PX]

;WITH ctea AS ( SELECT * , ROW_NUMBER() OVER ( PARTITION BY Initialcontract ORDER BY VersionNo ) AS rn FROM #T ),cteb AS ( SELECT * , CONVERT(DECIMAL(18,2),0.00) AS SumAmount FROM ctea WHERE rn = 1 UNION ALL SELECT ctea.* , CONVERT(DECIMAL(18,2),ctea.TotalAmount - cteb.TotalAmount) FROM ctea JOIN cteb ON cteb.rn + 1 = ctea.rn AND cteb.Initialcontract = ctea.Initialcontract ) SELECT Initialcontract , TotalAmount , VersionNo , SumAmount FROM cteb 结果是对的 老哥,我可以咨询你个问题吗?我有个地方看不懂, ctea.TotalAmount - cteb.TotalAmount 为什么会是高版本金额 - 低版本金额 例如 version 3 - versrion 2 怎么去判断的