表1
商品名 批量 批发单价
======================
a001 1 23.25
a001 100 22.08
a001 500 21.53
b001 1 9.64
b001 1200 90.21
b002 1 108.36
b002 1000 105.03
.
.
.
表2
销售流水号 商品名 批量 实际卖价
======================================
s001 a001 80 ?
s002 a001 350 ?
s003 b001 150 ?
s004 b001 500 ?
s005 b002 1300 ?
.
.
.
表1为商品批发价单价表,表2为实际售价表,请问如何求问号所示的实际卖价?
商品名 批量 批发单价
======================
a001 1 23.25
a001 100 22.08
a001 500 21.53
b001 1 9.64
b001 1200 90.21
b002 1 108.36
b002 1000 105.03
.
.
.
表2
销售流水号 商品名 批量 实际卖价
======================================
s001 a001 80 ?
s002 a001 350 ?
s003 b001 150 ?
s004 b001 500 ?
s005 b002 1300 ?
.
.
.
表1为商品批发价单价表,表2为实际售价表,请问如何求问号所示的实际卖价?
可否具体提供一下函数的写法或思路,我初学不久 :)
insert into t1 select 'a001',1,23.25
insert into t1 select 'a001',100,22.08
insert into t1 select 'a001',500,21.53
insert into t1 select 'b001',1,9.6
insert into t1 select 'b001',1200,9.21create table t2([id] int, [name] varchar(4),num int, mon float)
insert into t2 select 1,'a001',80,0
insert into t2 select 2,'a001',350,0
insert into t2 select 3,'b001',150,0
insert into t2 select 4,'b001',1500,0
--创建一个函数
create function getPrice(@name char(4),@num int)
returns float
as
begin
declare @price float
select top 1 @price=price
from t1
where name=@name and num<@num
order by num desc
return @price
endselect *,num*dbo.getPrice(name,num)
from t2
我试过了你的语句 好像不行吧 这样只会返回最小批量的单价呀
所以我用了 num*dbo.getPrice(name,num)--数量*单价
你再试试
insert into t1 select 'a001',1,23.25
insert into t1 select 'a001',100,22.08
insert into t1 select 'a001',500,21.53
insert into t1 select 'b001',1,9.6
insert into t1 select 'b001',1200,9.21create table t2([id] int, [name] varchar(4),num int, mon float)
insert into t2 select 1,'a001',80,0
insert into t2 select 2,'a001',350,0
insert into t2 select 3,'b001',150,0
insert into t2 select 3,'b001',500,0
insert into t2 select 4,'b001',1500,0
select t2.name,T2.num*(select top 1 price from t1 where name=t2.name and num<T2.num order by num DESC) from t2