22,206
社区成员
发帖
与我相关
我的任务
分享
;WITH CTE(ID,Num) AS (
SELECT 1,3 UNION ALL SELECT
2,6 UNION ALL SELECT
3,45 UNION ALL SELECT
4,23 UNION ALL SELECT
5,43 UNION ALL SELECT
6,53 UNION ALL SELECT
7,44 UNION ALL SELECT
8,33 UNION ALL SELECT
9,53 UNION ALL SELECT
10, 22),
xx(id,sm,allid) as (
select id,num sm,convert(varchar(max),id) allid from cte where num<=100
union all
select t.id, t.num + x.sm sm, x.allid+','+convert(varchar(max),t.id)
from cte t join xx x on t.id>x.id and t.num+x.sm<=100 )
select * from xx where sm=100 order by id
;WITH CTE(ID,Num) AS (
SELECT 1,3 UNION ALL SELECT
2,6 UNION ALL SELECT
3,45 UNION ALL SELECT
4,23 UNION ALL SELECT
5,43 UNION ALL SELECT
6,53 UNION ALL SELECT
7,44 UNION ALL SELECT
8,33 UNION ALL SELECT
9,53 UNION ALL SELECT
10, 22)
SELECT TOP 1 * FROM CTE a
INNER JOIN CTE b ON a.ID <> b.ID
ORDER BY ABS(100-a.Num-b.Num)