Sql 语句小课堂3：求不超过指定值的最大数字和

很多算法、命令都可以通过程序完成，但是要是为了某一个算法，单独去写个clr做支持，意义不大，算法总是千变万化的，所以，我们尝试用数据库本身去完成这些算法

例如有一组数，1、2、5、16.3、22.7、32.6、40、53.1。。。。

求这组数之和小于等100的最大和及其组合

写算法递归排序就好，那么数据库可以实现吗？其实也很简单

首先，我们先定义出这个数字队列成为一个CTE表

with t as (
	select convert(float,1) as n
	union all
	select 2
	union all
	select 5
	union all
	select 16.3
	union all
	select 22.7
	union all
	select 32.6
	union all
	select 40
	union all
	select 53.1
	union all
	select 14.4
	union all
	select 2.4
	union all
	select 7.2
	union all
	select 64
	union all
	select 32
)
select * from t


n
----------------------
1
2
5
16.3
22.7
32.6
40
53.1
14.4
2.4
7.2
64
32

(13 行受影响)

当然，如果你有正则clr或者split方法，就不用这么定义了

with t as (
	select convert(float,match) as n 
	from master.dbo.regexmatches('1,2,5,16.3,22.7,32.6,40,53.1,14.4,2.4,7.2,64,32','[^,]+')
)
select * from t

n
----------------------
1
2
5
16.3
22.7
32.6
40
53.1
14.4
2.4
7.2
64
32

(13 行受影响)

关于正则的clr调用，请参考正则表达式使模式匹配和数据提取变得更容易（David Banister），SQL Server 2005 CLR 调用Web Service需要注意的几个问题，好了，这个不是重点，我们继续看怎么实现求最大和

首先，单个值大于指定和的应该排除掉，然后模拟算数表达式以及结果

declare @val float
set @val=100

;with t as (
	select convert(float,match) as n 
	from master.dbo.regexmatches('1,2,5,16.3,22.7,32.6,40,53.1,14.4,2.4,7.2,64,32,111.1','[^,]+')
),t1 as (
	select n as max_val,convert(varchar(max),n) as exp_val,n as sum_val from t where n<=@val
)
select * from t1


max_val                exp_val                                                                                              sum_val
---------------------- ---------------------------------------------------------------------------------------------------- ----------------------
1                      1                                                                                                    1
2                      2                                                                                                    2
5                      5                                                                                                    5
16.3                   16.3                                                                                                 16.3
22.7                   22.7                                                                                                 22.7
32.6                   32.6                                                                                                 32.6
40                     40                                                                                                   40
53.1                   53.1                                                                                                 53.1
14.4                   14.4                                                                                                 14.4
2.4                    2.4                                                                                                  2.4
7.2                    7.2                                                                                                  7.2
64                     64                                                                                                   64
32                     32                                                                                                   32

(13 行受影响)

例如，max_val就是参与计算的最大数字，exp_val就是表达式，sum_val就是表达式的结果

然后我们通过CTE的递归特性来进行最大组合

declare @val float
set @val=100

;with t as (
	select convert(float,match) as n,sn as id 
	from master.dbo.regexmatches('1,2,5,16.3,22.7,32.6,40,53.1,14.4,2.4,7.2,64,32,111.1','[^,]+')
),t1 as (
	select id as max_id,convert(varchar(50),n) as exp_val,n as sum_val 
	from t where n<=@val
	union all
	select a.id
	,convert(varchar(50),b.exp_val+' + ' + convert(varchar,a.n))
	,a.n+b.sum_val 
	from t a,t1 b
	where a.id>b.max_id and a.n+b.sum_val<=@val
)
select * from t1
order by sum_val desc



max_id      exp_val                                            sum_val
----------- -------------------------------------------------- ----------------------
13          5 + 16.3 + 22.7 + 14.4 + 2.4 + 7.2 + 32            100
12          1 + 32.6 + 2.4 + 64                                100
11          2 + 5 + 32.6 + 53.1 + 7.2                          99.9
12          1 + 5 + 22.7 + 7.2 + 64                            99.9
13          1 + 2 + 22.7 + 32.6 + 2.4 + 7.2 + 32               99.9
13          2 + 16.3 + 40 + 2.4 + 7.2 + 32                     99.9
11          22.7 + 53.1 + 14.4 + 2.4 + 7.2                     99.8
13          2 + 16.3 + 32.6 + 14.4 + 2.4 + 32                  99.7
10          2 + 22.7 + 32.6 + 40 + 2.4                         99.7
13          5 + 53.1 + 2.4 + 7.2 + 32                          99.7

(1692 行受影响)

这个代码的思路也很简单了，获取数字，并给每个数字进行编号，然后通过递归进行关联，使用的数字的编号大于已有组合的数字id，且和不得大于指定值，然后组合出表达式

当然，由于递归限制，参与运算的数字不能大于100个，另外就是，数字越多效率越低，毕竟这个算是穷举法了