22,207
社区成员
发帖
与我相关
我的任务
分享
with t(cnt1, cnt3) as
(
select 1,20 union all
select 2,40 union all
select 3,15 union all
select 4,5 union all
select 5,20
)
select top 1 * from (
select cnt1, cnt3
, (select SUM(cnt3) from t b where b.cnt1<=a.cnt1) cnt_sum
from t a
) aa
where cnt_sum<=80
order by cnt_sum desc
SELECT TOP 1 *
FROM
(SELECT *, SUM(cnt3) OVER (ORDER BY cnt) AS subcnt FROM tab1) AS t
WHERE t.subcnt<80
ORDER BY cnt DESC;
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([cnt1] int,[cnt3] int)
Insert #T
select 1,20 union all
select 2,40 union all
select 3,15 union all
select 4,5 union all
select 5,20
Go
--测试数据结束
;WITH cte AS (
Select *,cnt3 AS sumcnt from #T WHERE cnt1=1
UNION ALL
SELECT #T.*,sumcnt+#T.cnt3 FROM #T JOIN cte ON cte.cnt1+1 = #T.cnt1 WHERE sumcnt+#T.cnt3<=80
)
SELECT *
FROM #T
WHERE cnt1 = ( SELECT MAX(cnt1)
FROM cte
)