/*一个数相加等于50*/
select * from tb where sl=50
/*两个数相加等于50*/
select * from tb a,tb b where a.sl<b.sl and a.sl+b.sl=50
....
依此类推
select * from tb where sl=50
/*两个数相加等于50*/
select * from tb a,tb b where a.sl<b.sl and a.sl+b.sl=50
....
依此类推
解决方案 »
- 触发器问题
- MSSQL如何得知一个存储过程被修改的时间呢
- 求条SQL语句
- 向高手求救!SQL2000不能在SQL2005上执行delete,求助~
- SQLSERVER查询
- 求高手帮俺看看如何优化这段存储过程代码,谢谢。
- 关于异构数据库的访问
- "Select top 10 Topic,num from law where left(num,2)=14"
- sql7.0我想把一张表完全(包括结构和内容)复制到另一张新表中,有什么简单方法?代码。谢谢!
- 请问:如何在trigger中写语句,如果出现异常(如数据不对),怎么返回提示消息到主程序
- sql2000数据库的存储过程如何调用oracle9i数据库中表?
- help!
create table tb(id varchar(10),length numeric(10,2))
insert tb select 1,200
union all select 2,410
union all select 3,300
union all select 4,310
union all select 5,100
union all select 6,500
union all select 7,480
union all select 8,600
go--查询函数
create function fn_search(
@Num numeric(10,2),
@step numeric(10,2)
)
returns @r table (gid varchar(10),id varchar(10),length numeric(10,2))
as
begin
declare @t table (gid int identity,id varchar(8000),id1 varchar(10),length numeric(10,2),level int)
declare @l int select @step=@num+@step,@l=0 insert @r select '',id,length from tb where length between @Num and @step
insert @t select ','+id+',',id,length,@l from tb where length<@step
while @@rowcount>0
begin
insert @r select distinct case when a.id=b.id then gid+'.' else gid end,a.id,a.length
from tb a,(
select gid=rtrim(gid)
,a.id,a.length,id1=b.id from tb a,@t b
where b.level=@l
and b.id1<a.id
and a.length between @num-b.length and @step-b.length
)b where a.id=b.id or charindex(','+a.id+',',b.id1)>0
set @l=@l+1
insert @t select b.id+a.id+',',a.id,a.length+b.length,@l
from tb a,@t b
where b.level=@l-1
and b.id1<a.id
and a.length<@step-b.length
end
--生成组号
declare @id decimal,@gid varchar(10),@i varchar(10)
set @gid=0
update @r set @gid=case when @id=gid then @gid else @gid+1 end
,@i=case when right(gid,1)='.' then @i-1 else 0 end
,gid=case when @i=0 then @gid else @gid+@i end
,@id=gid
where gid>''
lb_exit:
return
end
go--调用测试1
select * from dbo.fn_search(600,0)
go--删除测试
drop table tb
drop function fn_search/*--测试结果gid id length
8 600.00
1 5 100.00
1-1 6 500.00
2 1 200.00
2 3 300.00
2-1 5 100.00(所影响的行数为 6 行)--*/
三个的时候应该怎样?
是这样的吗
select * from tb a,tb b,tb c where a.sl<(b.sl+c.sl) and a.sl+b.sl+c.sl=50
另外,你的算法在源表中的 存在sl相等的行时,会导致很多重复的组合.
比如存在下面的记录时:
dm sl
A 25
A 25
我希望得到是组合,而不是排列.
三个的时候应该怎样?
是这样的吗
=====>为防止数据重复加一主鍵代码如下:
select *,identity(int,1,1) as id into # from tb order by sl
/*一个数相加等于50*/
select * from # where sl=50
/*两个数相加等于50*/
select * from # a,# b where a.id<b.id and a.sl+b.sl=50
/*三个数相加等于50*/
select * from # a,# b,# c where a.id<b.id and b.id<c.id and a.sl+b.sl+c.sl=50
....
依此类推