create table t1(a1 int)
insert t1
select 1 union select 2 union
select 3 union select 4 union
select 5 union select 6 union
select 7 union select 8 union
select 9
--1。查一个数这和为15
select * from t1 where a1=15
--2。查2个数这和为15
select * from t1 a,t1 b where a.a1<b.a1 and a.a1+b.a1=15--3。查3个数这和为15
select * from t1 a,t1 b,t1 c where a.a1<b.a1 and b.a1<c.a1 and a.a1+b.a1+c.a1=15--4。查4个数这和为15
select * from t1 a,t1 b,t1 c,t1 d where a.a1<b.a1 and b.a1<c.a1 and c.a1<d.a1 and
a.a1+b.a1+c.a1+d.a1=15--5。查5个数这和为15
select * from t1 a,t1 b,t1 c,t1 d,t1 e where a.a1<b.a1 and b.a1<c.a1 and c.a1<d.a1 and d.a1<e.a1 and
a.a1+b.a1+c.a1+d.a1+e.a1=15--依此类推
insert t1
select 1 union select 2 union
select 3 union select 4 union
select 5 union select 6 union
select 7 union select 8 union
select 9
--1。查一个数这和为15
select * from t1 where a1=15
--2。查2个数这和为15
select * from t1 a,t1 b where a.a1<b.a1 and a.a1+b.a1=15--3。查3个数这和为15
select * from t1 a,t1 b,t1 c where a.a1<b.a1 and b.a1<c.a1 and a.a1+b.a1+c.a1=15--4。查4个数这和为15
select * from t1 a,t1 b,t1 c,t1 d where a.a1<b.a1 and b.a1<c.a1 and c.a1<d.a1 and
a.a1+b.a1+c.a1+d.a1=15--5。查5个数这和为15
select * from t1 a,t1 b,t1 c,t1 d,t1 e where a.a1<b.a1 and b.a1<c.a1 and c.a1<d.a1 and d.a1<e.a1 and
a.a1+b.a1+c.a1+d.a1+e.a1=15--依此类推
我提供的是一个解决的思路,可以按此思路做成一个存储过程(第一个参数是最大数,第二个参数是它们的和)
里面的语句可以 写成动态SQL语句,采用循环做法,大家看下还有什么好的思路。
http://community.csdn.net/Expert/topic/4115/4115940.xml?temp=1.046389E-02
---------不行,只是考虑了按从小到大依次相加,好多情况都没考虑周全
-----------------------------------------------------------------------
create table #t1(id int)
create table #t2(id int)
create table #t3(id int)
create table #t4(id int)
create table #t5(id int)
create table #t6(id int)
create table #t7(id int)
create table #t8(id int)
create table #t9(id int)insert into #t1 select 1 union select null
insert into #t2 select 2 union select null
insert into #t3 select 3 union select null
insert into #t4 select 4 union select null
insert into #t5 select 5 union select null
insert into #t6 select 6 union select null
insert into #t7 select 7 union select null
insert into #t8 select 8 union select null
insert into #t9 select 9 union select null
select
result = left(t.result,len(t.result)-1)
from
(select
case when a.id is null then '' else rtrim(a.id)+',' end+
case when b.id is null then '' else rtrim(b.id)+',' end+
case when c.id is null then '' else rtrim(c.id)+',' end+
case when d.id is null then '' else rtrim(d.id)+',' end+
case when e.id is null then '' else rtrim(e.id)+',' end+
case when f.id is null then '' else rtrim(f.id)+',' end+
case when g.id is null then '' else rtrim(g.id)+',' end+
case when h.id is null then '' else rtrim(h.id)+',' end+
case when i.id is null then '' else rtrim(i.id)+',' end as result
from
#t1 a,#t2 b,#t3 c,#t4 d,#t5 e,#t6 f,#t7 g,#t8 h,#t9 i
where
isnull(a.id,0)+isnull(b.id,0)+isnull(c.id,0)+isnull(d.id,0)+isnull(e.id,0)+
isnull(f.id,0)+isnull(g.id,0)+isnull(h.id,0)+isnull(i.id,0) = 15) t
order by
resultdrop table #t1 ,#t2 ,#t3 ,#t4 ,#t5 ,#t6 ,#t7 ,#t8 ,#t9 --输出结果
/*
1,2,3,4,5
1,2,3,9
1,2,4,8
1,2,5,7
1,3,4,7
1,3,5,6
1,5,9
1,6,8
2,3,4,6
2,4,9
2,5,8
2,6,7
3,4,8
3,5,7
4,5,6
6,9
7,8
*/
学习:)