首先感谢各位师兄关注此贴
实现功能
查询出字段 BL的累计值
问题简述
这是代码和查询出的数据
现在我想在这个数据的基础上对字段 BL 计算累计值
但是
如果我把这些数据作为子查询的话就不能进行 Order by 排序 了
我需要按照字段 BL 从大到小排序的情况下对 BL 进行累计值计算
===============================================================================================================
SQL 代码
- SQL code
DECLARE @X DECIMAL(18,2)SET @X=(SELECT SUM(vbamount) FROM Qms_SPidVListTab WHERE CONVERT(VARCHAR(10),VresultDt,120) >= '2012-01-01' AND CONVERT(VARCHAR(10),VresultDt,120)<= '2012-12-31' )SELECT * FROM (SELECT B1,SUM(B2)B2,CAST(SUM(B2)[email protected]*100 AS DECIMAL(18,2)) BL FROM ( SELECT a.id,b.badinfo,dbo.[findendsubStrOK](b.badinfo,':',1) as b1 ,CAST(dbo.[findendsubStrOK](b.badinfo,':',2) AS DECIMAL(18,2))as b2 FROM ( SELECT id, [badinfo] = CONVERT(xml,'<root><v>' + REPLACE([badinfo], '|', '</v><v>') + '</v></root>') FROM Qms_SPidVListTab WHERE CONVERT(VARCHAR(10),VresultDt,120) >= '2012-01-01' AND CONVERT(VARCHAR(10),VresultDt,120)<= '2012-12-31' ) A OUTER APPLY ( SELECT badinfo = N.v.value('.', 'varchar(100)') FROM A.[badinfo].nodes('/root/v') N(v) ) B WHERE B.badinfo<>'')TB1 GROUP BY B1 --ORDER BY SUM(B2) DESC --此处无法ORDER BY ，即在计算累计值时无规则可言)TB2
---------------------------------------------------------------------------
查询结果
===============================================================================================================
------解决方案--------------------
- SQL code
--try;WITH t AS(SELECT B1,SUM(B2)B2,CAST(SUM(B2)[email protected]*100 AS DECIMAL(18,2)) BL,ROW_id=ROW_NUMBER() OVER(ORDER BY CAST(SUM(B2)[email protected]*100 AS DECIMAL(18,2)) DESC)FROM ( SELECT a.id,b.badinfo,dbo.[findendsubStrOK](b.badinfo,':',1) as b1 ,CAST(dbo.[findendsubStrOK](b.badinfo,':',2) AS DECIMAL(18,2))as b2 FROM ( SELECT id, [badinfo] = CONVERT(xml,'<root><v>' + REPLACE([badinfo], '|', '</v><v>') + '</v></root>') FROM Qms_SPidVListTab WHERE CONVERT(VARCHAR(10),VresultDt,120) >= '2012-01-01' AND CONVERT(VARCHAR(10),VresultDt,120)<= '2012-12-31' ) A OUTER APPLY ( SELECT badinfo = N.v.value('.', 'varchar(100)') FROM A.[badinfo].nodes('/root/v') N(v) ) B WHERE B.badinfo<>'')TB1 GROUP BY B1 )SELECT *,(SELECT SUM(BL) FROM T WHERE ROW_id<=a.ROW_id) FROM T AS a