加工最近单价求函数 环境:SQL2000
来源: 视图view_wwjgjgdj
加工单位, 日期, 产品, 加工工序, 加工单价
traderid, billdate, materialid, vipbmclid, price
10001 ,2013-03-01 , 3001 , 1 , 2.3 ----前面4个字段重复
10001 ,2013-03-01 , 3001 , 1 , 2.7 ----前面4个字段重复
10001 ,2013-03-08 , 3001 , 2 , 3
10001 ,2013-06-05 , 3001 , 1 , 4
10001 ,2013-06-07 , 3001 , 1 , 3.3 ---完全重复
10001 ,2013-06-07 , 3001 , 1 , 3.3 ---完全重复
求函数function
vip_wwjgdjby(@traderid int ,@billdate datetime ,@materialid int ,@vipbmclid int )
注意:有可能视图view_wwjgjgdj 有得重复的行.
首先查询traderid,再查询materialid,vipbmclid ,再查询billdate, 后再取price的最大值
查询1 函数
select * from
vip_wwjgdjby('10001' ,'2013-03-01' ,'3001','1')
traderid, billdate, materialid, vipbmclid, price
10001 ,2013-03-01 , 3001 , 1 , 2.7
查询2
select * from
vip_wwjgdjby('10001' ,'2013-06-06' ,'3001','1')
traderid, billdate, materialid, vipbmclid, price
10001 ,2013-06-05 , 3001 , 1 , 4
查询3
select * from
vip_wwjgdjby('10001' ,'2013-06-06' ,'3001','2')
traderid, billdate, materialid, vipbmclid, price
10001 ,2013-03-08 , 3001 , 2 , 3
查询4
select * from
vip_wwjgdjby('10001' ,'2013-06-07' ,'3001','1')
traderid, billdate, materialid, vipbmclid, price
10001 ,2013-03-08 , 3001 , 1 , 3.3
------解决方案--------------------
CREATE TABLE view_wwjgjgdj(
traderid INT,
billdate DATETIME,
materialid INT,
vipbmclid INT,
price DECIMAL(9, 2)