27,580
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([name] nvarchar(21),[price] int)
Insert #T
select N'A',1 union all
select N'B',50 union all
select N'C',100
Go
--测试数据结束
DECLARE @allpprice INT = 140 --组合钱数
Select @allpprice=SUM(price)-@allpprice from #T --应少的钱数,这个应该是关联查询出来的
;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY price DESC) AS num FROM #T
),cteb AS (
SELECT name ,
CASE WHEN price - @allpprice >= 0 THEN price - @allpprice
ELSE 0
END AS price ,
price - @allpprice tempprice,
num
FROM ctea
WHERE num = 1
UNION ALL
SELECT ctea.name ,
CASE WHEN tempprice > 0 THEN ctea.price
ELSE ( CASE WHEN ctea.price + tempprice > 0 THEN ctea.price + tempprice
ELSE 0
END )
END AS price,
ctea.price + tempprice AS tempprice,
ctea.num
FROM ctea
JOIN cteb ON cteb.num + 1 = ctea.num
)
SELECT name,price FROM cteb