[sql server] 随机求和问题收集
http://topic.csdn.net/u/20090421/12/5113C4AB-4631-4DC9-BE9F-E6D88F0526D8.html解一 2000
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
) t
order by
newid()
解二 2005
--sql2005的一种解法：
if object_id('') is not null drop table [tb]
go
create table [tb]([ID] int,[NUM] int)
insert [tb]
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
go
--select * from
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()) a
where charindex('-'+rtrim(id)+'-','-'+path+'-')>0
--1.
/*
2 3
3 2
4 5
6 5
*/
--2.
/*
2 3
3 2
4 5
8 2
10 3
*/
--3....
页:
[1]