create table #m(ID int,cash money) insert into #m select 1,1000 insert into #m select 2,2000create table #r(ID int,cash money,rate decimal(9,5)) insert into #r select 1,900,0.1 insert into #r select 2,1000,0.3 insert into #r select 3,1500,0.12 insert into #r select 4,3000,0.15--查询 select A.* ,B.rate from #m A join #r B on A.cash>= B.cash and B.ID=(select max(ID) from #r where cash<A.cash)--结果 /* ID cash rate ----------- --------------------- ----------- 1 1000.0000 .10000 2 2000.0000 .12000(2 row(s) affected) */
select cash,cash*(select rate from #r where cash=a.cash) from #m as a
select m.cash*r.rate as cash from #m as m,#r as r where m.id = r.id
create table #m(ID int,cash money) insert into #m select 1,1000 insert into #m select 2,2000create table #r(ID int,cash money,rate decimal(9,5)) insert into #r select 1,900,0.1 insert into #r select 2,1000,0.3 insert into #r select 3,1500,0.12 insert into #r select 4,3000,0.15select #m.cash*(select #r.rate from (select max(cash) as cash from #r where cash<=#m.cash)T,#r where #r.cash=T.cash) from #m
insert into #m select 1,1000
insert into #m select 2,2000create table #r(ID int,cash money,rate decimal(9,5))
insert into #r select 1,900,0.1
insert into #r select 2,1000,0.3
insert into #r select 3,1500,0.12
insert into #r select 4,3000,0.15--查询
select A.*
,B.rate
from #m A
join #r B on A.cash>= B.cash
and B.ID=(select max(ID) from #r where cash<A.cash)--结果
/*
ID cash rate
----------- --------------------- -----------
1 1000.0000 .10000
2 2000.0000 .12000(2 row(s) affected)
*/
insert into #m select 1,1000
insert into #m select 2,2000create table #r(ID int,cash money,rate decimal(9,5))
insert into #r select 1,900,0.1
insert into #r select 2,1000,0.3
insert into #r select 3,1500,0.12
insert into #r select 4,3000,0.15select #m.cash*(select #r.rate from (select max(cash) as cash from #r where cash<=#m.cash)T,#r where #r.cash=T.cash)
from #m