给个思路 , 就是 写个函数 , 计算出需要的 exchange 集合 , 然后再SQL查询哦
解决方案 »
- SQL企业管理器中,同一个数据库的进程允许多少个呢???
- 请问下怎样查询表中两个字段的差的最大的那一行记录?
- 菜鸟 请教大师 一个简单问题 下班结帖!
- sql问题
- EXCEL统计使用次数表
- 进者有分,SQL问题简单问题。
- 多表联合查询问题
- [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server 不存在或访问被拒绝--请教高手们
- 菜鸟提问:怎么样用软盘或者移动存储器,把一个机器上的数据库copy到别的机器
- 日语下应该选哪个collation
- 为什么我在原来的数据库新建了很多表,并且也插入了很多数据,但是.MDF文件大小没有变化呢
- SQL触发器
select id
from tb
where value=@valunion
-- 2条相加相等的情况
select id=cast(a.id as varchar)+','+cast(b.id as varchar)
from tb a
join tb b on a.id!=b.id and a.value<@val and b.value<@val
where a.value+b.value=@valunion
-- 3条相加相等的情况
select id=cast(a.id as varchar)+','+cast(b.id as varchar)+','+cast(c.id as varchar)
from tb a
join tb b on a.id!=b.id and a.value<@val and b.value<@val
join tb c on a.id!=c.id and b.id!=c.id and a.value<@val and c.value<@val
where a.value+b.value+c.value=@val..
id int,
exchange numeric(28,4)
)insert exch select
1, 2.5
union all select
2, 2
union all select
3, 1.7
union all select
4, 1.5
union all select
5, 1.2
union all select
6, 1 go
create proc pr_Get
@Num numeric(36,4)
asdeclare @t table (
exchange numeric(36,4),
Ids varchar(2000)
)declare @t1 table (
exchange numeric(36,4),
Ids varchar(2000)
)insert @t select exchange,cast(Id as varchar)
from exchwhile not exists (select 1 from @t where exchange between @Num*0.8 and @Num*1.2)
begin
delete @t1
insert @t1
select exchange=a.exchange+b.exchange,a.Ids+','+cast(b.id as varchar)
from @t a,exch b
where a.exchange+b.exchange<=@Num*1.2
delete @t
insert @t select * from @t1
endif exists (select 1 from @t where exchange between @Num*0.8 and @Num*1.2)
begin
select top 1 result=ids from @t order by abs(exchange-@Num)
end
else
select result='没有结果'go
--测试
exec pr_Get 3
result
-----------------------------------------
1(所影响的行数为 1 行)--测试2
exec pr_Get 4
result
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,4(所影响的行数为 1 行)
是0%--20%,我理解成-20%--20%了ps:20%是否太大了alter proc pr_Get
@Num numeric(36,4)
asdeclare @t table (
exchange numeric(36,4),
Ids varchar(2000)
)declare @t1 table (
exchange numeric(36,4),
Ids varchar(2000)
)insert @t select exchange,cast(Id as varchar)
from exchwhile not exists (select 1 from @t where exchange between @Num*0.8 and @Num*1.2)
begin
delete @t1
insert @t1
select exchange=a.exchange+b.exchange,a.Ids+','+cast(b.id as varchar)
from @t a,exch b
where a.exchange+b.exchange<=@Num*1.2
delete @t
insert @t select * from @t1
endif exists (select 1 from @t where exchange between @Num and @Num*1.2)
begin
select top 1 result=ids from @t order by abs(exchange-@Num)
end
else
select result='没有结果'go
@Num numeric(36,4)
asdeclare @t table (
exchange numeric(36,4),
Ids varchar(2000)
)declare @t1 table (
exchange numeric(36,4),
Ids varchar(2000)
)insert @t select exchange,cast(Id as varchar)
from exchwhile not exists (select 1 from @t where exchange between @Num and @Num*1.2)
begin
delete @t1
insert @t1
select exchange=a.exchange+b.exchange,a.Ids+','+cast(b.id as varchar)
from @t a,exch b
where a.exchange+b.exchange<=@Num*1.2
delete @t
insert @t select * from @t1
endif exists (select 1 from @t where exchange between @Num and @Num*1.2)
begin
select top 1 result=ids from @t order by abs(exchange-@Num)
end
else
select result='没有结果'go
create table tb (id int identity,e float)
insert tb select 2.5
insert tb select 2
insert tb select 1.7
insert tb select 1.5
insert tb select 1.2
insert tb select 1
go
create proc gett(@s float)
as begin
declare @t table(e float,idc varchar(30))
declare @t1 table(e float,idc varchar(30))
insert @t select e,rtrim(id) from tb where e <=@s*1.2
if not exists(select 1 from @t)
begin
select * from tb where e = (select min(e) from tb)
return
end
while not exists(select 1 from @t where e = @s ) and exists(select 1 from @t)
begin
delete @t1
insert @t1 select * from @t
delete @t
insert @t select a.e+b.e,a.idc+','+rtrim(b.id) from @t1 a,tb b where a.e+b.e <=1.2*@s
end
if exists(select 1 from @t where @s = e )
select * from @t where e = @s
else
select * from @t1 where e = (select min(e) from @t1)
end
go
exec gett 3
/*
e idc
----------------------------------------------------- ------------------------------
3.0 2,6
3.0 4,4
3.0 6,2
*/
exec gett 3.15
/*
e idc
----------------------------------------------------- ------------------------------
3.0 6,6,6
*/
go
drop table tb
go
drop proc gett
结果应该是:4,5,5 或 3,5,6
create table exch (
id int,
exchange numeric(28,4)
)insert exch select
1, 2.5
union all select
2, 2
union all select
3, 1.7
union all select
4, 1.5
union all select
5, 1.2
union all select
6, 1 gocreate proc pr_Get
@Num numeric(36,4)
asdeclare @t table (
exchange numeric(36,4),
Ids varchar(2000)
)declare @t1 table (
exchange numeric(36,4),
Ids varchar(2000)
)insert @t select exchange,cast(Id as varchar)
from exchwhile not exists (select 1 from @t where exchange between @Num and @Num*1.2)
begin
delete @t1
insert @t1
select exchange=a.exchange+b.exchange,a.Ids+','+cast(b.id as varchar)
from @t a,exch b
where a.exchange+b.exchange<=@Num*1.2
delete @t
insert @t select * from @t1
endif exists (select 1 from @t where exchange between @Num and @Num*1.2)
begin
select top 1 result=ids from @t order by abs(exchange-@Num)
end
else
select result='没有结果'go
pr_get 3.9
/*
result
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,4*/
go
drop table exch
go
drop proc pr_get
--我也貼一個,ms不用考慮20%這個限制create table T(id int, exchange numeric(18,2))
insert into T select 1,2.5
insert into T select 2,2
insert into T select 3,1.7
insert into T select 4,1.5
insert into T select 5,1.2
insert into T select 6,1
Go
Create function fn_priority(@id varchar(100))
returns varchar(100)
AS
begin
declare @str varchar(100), @re varchar(100)
declare @t table ([id] int)
set @re=''
set @str =@id
while charindex(',', @str)>0
begin
insert into @t select left(@str, charindex(',',@str)-1)
set @str=right(@str,len(@str)-charindex(',',@str))
end
insert into @t select @str select @re=@re+','+rtrim(id) from (select top 100 percent id from @t order by id) t
if left(@re,1)=','
set @re=stuff(@re,1,1,'')
return @re
endGOCreate Procedure usp_test
@basic numeric(18,2)
AS
declare @min numeric(18,2),@min_b numeric(18,2)
declare @t table(id varchar(100), exchange numeric(18,2))
declare @tmp table(id varchar(100),exchange numeric(18,2))select convert(varchar(100),id) as id ,exchange into #t from T
select @min_b=min(exchange) from #t where exchange>=@basic
insert into @tmp select * from #t where exchange=@min_b
if exists(select 1 from #t where exchange=@basic)
insert into @t select * from #t where exchange=@basic
else
Begin
delete #t where exchange>@min_b while (select count(*) from #t)>1
begin
select @min=min(exchange) from #t where exchange>=@basic
if @min<@min_b
begin
set @min_b=@min
delete @tmp
insert into @tmp select * from #t where exchange=@min_b
end
delete #t where exchange>@min
if (select count(*) from #t)=1
break select rtrim(a.id)+','+rtrim(b.id) as id, a.exchange+ b.exchange as exchange
into #tmp
from #t a, T b
if exists(select 1 from #tmp where exchange=@basic)
begin
insert into @t select * from #tmp where exchange=@basic
drop table #tmp
break
end
else
begin
truncate table #t
insert into #t select * from #tmp
drop table #tmp
end
endif (select count(*) from @t)=0
begin
if (select min(exchange) from #t)>=@min_b
insert into @t select * from @tmp
else
insert into @t select * from #t
endEndselect distinct dbo.fn_priority(id) as id, exchange from @tGOexec usp_test 3
/*
id exchange
--------------------
2,6 3.00
4,4 3.00*/drop table T
drop function dbo.fn_priority
drop proc usp_test
--结果为:2,5
exec usp_test 3.15
/*
id exchange
---------------------------------------------------------------------------------------------------- --------------------
6,6,6,6 4.00
*/
set nocount on
create table tb (id int identity,e dec(10,2))
insert tb select 2.5
insert tb select 2
insert tb select 1.7
insert tb select 1.5
insert tb select 1.2
insert tb select 1
go
create proc gett(@s dec(10,2))
as begin
declare @t table(e dec(10,2),id int,idc varchar(30),cc int)
declare @t1 table(e dec(10,2),id int,idc varchar(30),cc int)
declare @t2 table(e dec(10,2),id int,idc varchar(30),cc int)
insert @t select e,id,rtrim(id),1 from tb where e <=@s*1.2
insert @t2 select * from @t1
if not exists(select 1 from @t)
begin
select r=@s,e,id,cc = 1 from tb where e = (select min(e) from tb)
return
end
while not exists(select 1 from @t where e = @s ) and exists(select 1 from @t)
begin
delete @t1
insert @t1 select * from @t
delete @t
insert @t select a.e+b.e,b.id,a.idc+','+rtrim(b.id),a.cc+1 from @t1 a,tb b where a.e+b.e <=1.2*@s and a.id<=b.id
insert @t2 select * from @t
end
select r=@s,a.e,idc,a.cc from @t2 a,(
select e,min(cc) cc from @t2 where abs(e-@s) = (select min(abs(e-@s)) from @t2) group by e
) b where a.e = b.e and a.cc = b.cc
end
go
exec gett 0.8
/*
r e id cc
------------ ------------ ----------- -----------
.80 1.00 6 1*/
exec gett 3
/*
r e idc cc
------------ ------------ ------------------------------ -----------
3.00 3.00 2,6 2
3.00 3.00 4,4 2
*/
exec gett 3.15
/*
r e idc cc
------------ ------------ ------------------------------ -----------
3.15 3.20 2,5 2
3.15 3.20 3,4 2
*/
exec gett 3.9
/*
r e idc cc
------------ ------------ ------------------------------ -----------
3.90 3.90 4,5,5 3
3.90 3.90 3,5,6 3
*/
go
drop table tb
go
drop proc gett
假如
table 只insert 1筆
create table tb (id int identity,e dec(10,2))
insert tb select 3exec gett 3好像不出結果~~~
insert @t select e,id,rtrim(id),1 from tb where e <=@s*1.2
insert @t2 select * from @t1
==》
insert @t select e,id,rtrim(id),1 from tb where e <=@s*1.2
insert @t2 select * from @t --就这多了个1