随机查询求和问题(急)
现在有一个表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?
[解决办法]
背包问题.
[解决办法]
背包算法.
[解决办法]
随机取一行,再用背包算法获得其他行.
[解决办法]
关注！
[解决办法]
关注...
[解决办法]
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()
[解决办法]
關注,學習.
[解决办法]
关注！！
[解决办法]
学习
[解决办法]
--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....
[解决办法]
砖砖很强大！
[解决办法]
DECLARE @t TABLE(ID INT, NUM INT)INSERT @t SELECT 1 ,5UNION ALL SELECT 2 ,3UNION ALL SELECT 3 , 2UNION ALL SELECT 4 , 5UNION ALL SELECT 5 , 4UNION ALL SELECT 6, 5UNION ALL SELECT 7 ,3UNION ALL SELECT 8 , 2UNION ALL SELECT 9 , 4UNION ALL SELECT 10, 3 DECLARE @amt INTSET @amt = 15;WITH fc AS( SELECT TOP 1 1 n,ID FID,ID,CAST(ID AS VARCHAR(1000)) p, NUM FROM @t WHERE NUM<=@amt ORDER BY NEWID() UNION ALL SELECT b.n+1,b.ID,a.ID,CAST(RTRIM(b.p) + ',' + RTRIM(a.ID) AS VARCHAR(1000)) , b.NUM + a.NUM FROM @t a,fc b WHERE a.NUM + b.NUM <= @amt AND CHARINDEX(',' + RTRIM(a.id) + ',',',' + b.p + ',')=0)SELECT * INTO #1 FROM fc WHERE num = 15SELECT * FROM @t WHERE CHARINDEX(',' + RTRIM(ID) +',' ,',' + RTRIM((SELECT TOP 1 p FROM #1 ORDER BY NEWID())) + ',')>0/*4 56 57 38 2*/DROP TABLE #1
[解决办法]
原来有人写了..
其实一样
[解决办法]
学习。。。
[解决办法]
create table tb(ID int,NUM int)insert into tb select 1,5 insert into tb select 2,3 insert into tb select 3,2 insert into tb select 4,5 insert into tb select 5,4 insert into tb select 6,5 insert into tb select 7,3 insert into tb select 8,2 insert into tb select 9,4 insert into tb select 10,3 gowith cte as(select top 1 id,num,num as s,convert(varchar(20),id)+',' as k from tb order by newid()union allselect a.id,a.num,a.num+b.s as s,convert(varchar(20),b.k+convert(varchar,a.id))+',' as k from tb a inner join cte b on a.num+b.s<=15 where charindex(convert(varchar,a.id)+',',b.k)=0 )select * from tb a where (select top 1 ','+k from cte where s=15)like '%,'+convert(varchar,id)+',%'godrop table tb/*ID NUM----------- -----------3 25 48 29 410 3(5 行受影响)*/
[解决办法]
嗯, 是我用错词了. 不是全排列.
[解决办法]
老大们都来啦
占位学习
[解决办法]
最近做sybase做的有点怕了, 一条语句, 往往一上生产就I/0过大,被kill掉了, 写临时表写顺了..
[解决办法]
学习了
[解决办法]
学习...
[解决办法]
学习.....
[解决办法]
不错
[解决办法]
蓉儿那个运行起来速度挺快的。
[解决办法]
学习
[解决办法]
其实可以这样做：
你分为3个数：
4个数：
5个数：
6个数:（那是不可能）因为：最小的6个数3+2+3+2+4=17>15
[解决办法]
然后写个存储过程就ok了
[解决办法]
或者用游标来
[解决办法]
学习~~~~~~~~~~~~~~~~~~
[解决办法]
学习
[解决办法]
学习
[解决办法]
菜鸟路过
[解决办法]
.
[解决办法]
挺好
[解决办法]
学习一下，顺便顶一下
[解决办法]
mark
[解决办法]
看了才知道自己是多么无知！
[解决办法]
菜鸟路过
[解决办法]
路过，学习下！~
[解决办法]
菜鸟路过
[解决办法]
还在学习中!
有机会的话一定成为高手！
[解决办法]
我是一家美资猎头公司的HR，现在需要招聘一位精通技术研发职位的猎头顾问，如果各位精英认识这样的猎头，请与我联系，谢谢！
联系方式：msn：fcyhappy2008@hotmail.com
[解决办法]
if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([ID] int,[NUM] int)insert [tb]select 1,1 union allselect 2,3 union allselect 3,2 union allselect 4,1 union allselect 5,4 union allselect 6,5 union allselect 7,3 union allselect 8,2 union allselect 9,1 union allselect 10,1go--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
[解决办法]