在有限多的不大于100的正整数中,找出尽量多个相加起来值介于98~102之间的组合...

xiaoxiao2021-03-01  3

问题描述:

在有限多的不大于100的正整数中,找出尽量多个相加起来值介于98102之间的组合。

组合的个数限制在2 3

比如有数字(39401551717……N)数字可以有重复。

找出的组合有(5050),(214139),(4850)……M。在上面的数字在组合中每次只能出现一次(比如数字中40只出现过一次,那在组合中也只能出现一次。17出现过两次那在组合中最多两次)

实现现代码

-- 测试数据, value 列放要处理的数(此处随机生成100个数据)

DECLARE @t TABLE(

id int identity,

value int)

INSERT @t SELECT TOP 100 CHECKSUM(NEWID()) % 100 FROM syscolumns

--=====================================================

-- 组合处理

--=====================================================

DECLARE @r TABLE(

id int IDENTITY,

vs varchar(100))

DECLARE @tid TABLE(id int PRIMARY KEY)

IF OBJECT_ID('tempdb..#1') IS NOT NULL

DROP TABLE #1

SELECT

id1 = A.id, value1 = A.value,

id2 = B.id, value2 = B.value,

value = A.value + B.value,

flag = CASE WHEN A.value + B.value BETWEEN 98 AND 102 THEN 1 ELSE 0 END

INTO #1

FROM @t A, @t B

WHERE A.id < B.id

DECLARE tb CURSOR STATIC LOCAL

FOR

SELECT id1, id2, id3, vs

FROM(

-- 二次组合的

SELECT flag = 2,

A.id1, A.id2, id3 = NULL,

vs = RTRIM(A.value1) + ',' + RTRIM(A.value2)

FROM #<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="True" sourcevalue="1" unitname="a">1 A</chmetcnv>

WHERE flag = 1

UNION ALL

-- 三次组合的

SELECT flag = 3,

A.id1, A.id2, id3 = B.id,

RTRIM(A.value1) + ',' + RTRIM(A.value2) + ',' + RTRIM(B.value)

FROM #<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="True" sourcevalue="1" unitname="a">1 A</chmetcnv>, @t B

WHERE A.id2 < B.id

AND A.value + B.value BETWEEN 98 AND 102

)A

ORDER BY flag DESC

-- 仅选出复合需求的数据(过滤重复数据)

DECLARE @id1 int, @id2 int, @id3 int, @values varchar(100)

OPEN tb

FETCH tb INTO @id1, @id2, @id3, @values

WHILE @@ROWCOUNT > 0

BEGIN

IF NOT EXISTS(

SELECT * FROM @tid WHERE id IN(@id1, @id2, @id3))

BEGIN

INSERT @r VALUES(@values)

INSERT @tid SELECT *

FROM(

SELECT id = @id1 UNION ALL SELECT @id2 UNION ALL SELECT @id3

)A WHERE id > 0

END

FETCH tb INTO @id1, @id2, @id3, @values

END

CLOSE tb

DEALLOCATE tb

-- 显示结果

SELECT vs FROM @r

相关资源:微信小程序源码-合集1.rar
转载请注明原文地址: https://www.6miu.com/read-3650237.html

最新回复(0)