现在有一个表Table1进行随机查询, 检索结果记录条数不限, 但是要求Num求和结果为一个固定值,如结果为15
ID NUM
1 5
2 3
3 2
4 5
5 4
6 5
7 3
8 2
9 4
10 3
------解决方案--------------------
先看下
------解决方案--------------------
随机返回一组ID，ID对应的Num之和为15?
------解决方案--------------------
背包问题.
------解决方案--------------------
背包算法.
------解决方案--------------------
随机取一行,再用背包算法获得其他行.
------解决方案--------------------
关注！
------解决方案--------------------
关注...
------解决方案--------------------
- SQL code
declare @t table(ID int,NUM int)insert into @t select 1,5 union all select 2,3 union all select 3,2 union all select 4,5 union all select 5,4 union all select 6,5 union all select 7,3 union all select 8,2 union all select 9,4 union all select 10,3 select top 1 rtrim(ID1) +isnull(','+rtrim(ID2),'') +isnull(','+rtrim(ID3),'') +isnull(','+rtrim(ID4),'') +isnull(','+rtrim(ID5),'') +isnull(','+rtrim(ID6),'')from (select a.id as id1, b.id as id2, c.id as id3, d.id as id4, e.id as id5, f.id as id6 from @t a, (select * from @t union select null,null) b, (select * from @t union select null,null) c, (select * from @t union select null,null) d, (select * from @t union select null,null) e, (select * from @t union select null,null) f where a.id<isnull(b.id,995) and isnull(b.id,995)<isnull(c.id,996) and isnull(c.id,996)<isnull(d.id,997) and isnull(d.id,997)<isnull(e.id,998) and isnull(e.id,998)<isnull(f.id,999) and (a.NUM+isnull(b.NUM,0)+isnull(c.NUM,0)+isnull(d.NUM,0)+isnull(e.NUM,0)+isnull(f.NUM,0))=15 ) torder by newid()
------解决方案--------------------
關注,學習.
------解决方案--------------------
关注！！
------解决方案--------------------
学习
------解决方案--------------------
- SQL code
--sql2005的一种解法：if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([ID] int,[NUM] int)insert [tb]select 1,5 union allselect 2,3 union allselect 3,2 union allselect 4,5 union allselect 5,4 union allselect 6,5 union allselect 7,3 union allselect 8,2 union allselect 9,4 union allselect 10,3go--select * from [tb]with szx as( select *,path=cast(id as varchar(8000)),total=num from tb union all select b.id,b.num,a.path+'-'+rtrim(b.id),a.total+b.num from szx a join tb b on a.id<b.id and a.total<15)select id,num from tb,(select top 1 path from szx where total=15 order by newid()) awhere charindex('-'+rtrim(id)+'-','-'+path+'-')>0--1./*2 33 24 56 5*/--2./*2 33 24 58 210 3*/--3....
------解决方案--------------------
砖砖很强大！
------解决方案--------------------