22,210
社区成员
发帖
与我相关
我的任务
分享
insert into #test_data
select 1 union all
select 1 union all
select 1 union all
select -3 union all
select 4 union all
select -4 union all
select 8 union all
select 7 union all
select 6 union all
select -8 union all
select -3 union all
select -3
-- 测试数据(要有主键或唯一键)
create table #test_data(
id int identity primary key,
value int
);
insert into #test_data
select 1 union all
select 1 union all
select 1 union all
select -3 union all
select 4 union all
select -4 union all
select 8 union all
select 7 union all
select 6 union all
select -8 union all
select -3 union all
select -3
;
GO
WITH DATA AS( -- 所有的组合
SELECT id, value,
ids = CONVERT(varchar(max), '<c>' + rtrim(id) + '</c>'), level = 0
FROM #test_data
UNION ALL
SELECT A.id, A.value + DATA.value,
DATA.ids + '<c>' + rtrim(A.id) + '</c>', DATA.level + 1
FROM DATA, #test_data A
WHERE DATA.value <> 0
AND DATA.id < A.id -- 通过主键或唯一键避免在同一组中多次使用同一记录
),
REQ AS( -- 可抵扣的组合
SELECT id=ROW_NUMBER()OVER(ORDER BY level, id), ids = CONVERT(xml, ids)
FROM DATA
WHERE value = 0
),
DEL_REQ AS( -- 筛选方案选择,按照组合顺序选择最早能抵扣的组合
SELECT REQ.ids, next_id = 2,
flag_del = 1, ids_next = REQ.ids
FROM REQ
WHERE id = 1
UNION ALL
SELECT REQ.ids, DEL_REQ.next_id + 1,
ISNULL(FLAG_DEL.value, 1),
CASE FLAG_DEL.value WHEN 0 THEN DEL_REQ.ids_next
ELSE CONVERT(xml, CONVERT(nvarchar(max), DEL_REQ.ids_next) + CONVERT(nvarchar(max), REQ.ids))
END
FROM REQ
INNER JOIN DEL_REQ ON REQ.id = DEL_REQ.next_id
OUTER APPLY(
SELECT 0 as flag WHERE EXISTS(
SELECT * FROM(
SELECT id = T.c.value('.[1]', 'int')
FROM REQ.ids.nodes('/c/text()') T(c)
) X WHERE DEL_REQ.ids_next.exist('/c[text()=sql:column("X.id")]') = 1
)
)FLAG_DEL( value )
),
DEL_ID AS( -- 最终确定删除的 主键或唯一键 列表
SELECT id = T.c.value('.', 'int')
FROM(
SELECT TOP 1 ids_next
FROM DEL_REQ
WHERE flag_del = 1
ORDER BY next_id DESC
) D
CROSS APPLY D.ids_next.nodes('/c/text()') T(c)
)
-- 删除
-- DELETE DATA FROM #test_data DATA, DEL_ID WHERE DATA.id = DEL_ID.id
-- 查看删除列表
SELECT ids FROM DEL_REQ WHERE flag_del = 1
OPTION( MAXRECURSION 0 )
GO
SELECT * FROM #test_data;
-- 删除测试数据
DROP TABLE #test_data
要什么样的结果?