急求有难度的最近加工单价函数
加工最近单价求函数 环境: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)
)
GO
INSERT INTO view_wwjgjgdj
SELECT 10001, '2013-03-01', 3001, 1, 2.3
UNION ALL SELECT 10001, '2013-03-01', 3001, 1, 2.7
UNION ALL SELECT 10001, '2013-03-08', 3001, 2, 3
UNION ALL SELECT 10001, '2013-06-05', 3001, 1, 4
UNION ALL SELECT 10001, '2013-06-07', 3001, 1, 3.3
UNION ALL SELECT 10001, '2013-06-07', 3001, 1, 3.3
GO
--DROP FUNCTION vip_wwjgdjby
CREATE FUNCTION vip_wwjgdjby(@traderid int ,@billdate datetime ,@materialid int ,@vipbmclid int )
RETURNS @TBL TABLE(traderid INT, billdate DATETIME, materialid INT, vipbmclid INT, price DECIMAL(9, 2))
AS
BEGIN
--DECLARE @TBL TABLE(traderid INT, billdate DATETIME, materialid INT, vipbmclid INT, price DECIMAL(9, 2))
INSERT INTO @TBL
SELECT TOP 1 traderid, billdate, materialid, vipbmclid, price
FROM view_wwjgjgdj WHERE traderid=@traderid AND billdate<=@billdate AND materialid=@materialid AND vipbmclid=@vipbmclid
ORDER BY billdate DESC, price DESC
RETURN --@TBL
END
--查询1 函数
select * from vip_wwjgdjby('10001' ,'2013-03-01' ,'3001','1')
/*
traderid billdate materialid vipbmclid price
----------- ----------------------- ----------- ----------- ---------------------------------------
10001 2013-03-01 00:00:00.000 3001 1 2.70
(1 行受影响)
*/
--查询2
select * from vip_wwjgdjby('10001' ,'2013-06-06' ,'3001','1')
/*
traderid billdate materialid vipbmclid price
----------- ----------------------- ----------- ----------- ---------------------------------------
10001 2013-06-05 00:00:00.000 3001 1 4.00
(1 行受影响)
*/
--查询3
select * from vip_wwjgdjby('10001' ,'2013-06-06' ,'3001','2')
/*
traderid billdate materialid vipbmclid price
----------- ----------------------- ----------- ----------- ---------------------------------------
10001 2013-03-08 00:00:00.000 3001 2 3.00
*/
查询4
select * from vip_wwjgdjby('10001' ,'2013-06-07' ,'3001','1')
/*
traderid billdate materialid vipbmclid price
----------- ----------------------- ----------- ----------- ---------------------------------------
10001 2013-06-07 00:00:00.000 3001 1 3.30
(1 行受影响)
*/