在一个项目中,,
由于产品的单价经过会变,,但是在为员工计算提成时,需要使用做货时的单价来计算他的工资。
这就要找到最近的单价来进行计算。
(以下代码在 SQL2008R2中测试)
CREATE TABLE #TEMP_A (
CPBH VARCHAR(10),
RQ SMALLDATETIME,
DJ MONEY
)
CREATE TABLE #TEMP_B(
ID INT IDENTITY(1,1),
CPBH VARCHAR(10),
RQ SMALLDATETIME,
DJ MONEY
)
INSERT INTO #TEMP_A
VALUES
('001','2011-01-01',1),
('001','2011-03-01',2),
('001','2011-06-01',3),
('001','2011-07-01',4),
('001','2011-09-01',5),
('002','2011-01-01',1),
('002','2011-02-01',2),
('002','2011-04-01',3),
('002','2011-07-01',4),
('002','2011-09-01',5)
INSERT INTO #TEMP_B(CPBH,RQ)
VALUES
('001','2011-01-05'),
('001','2011-02-02'),
('001','2011-05-01'),
('001','2011-08-01'),
('002','2011-01-05'),
('002','2011-03-05'),
('002','2011-05-05'),
('002','2011-10-05')
UPDATE #TEMP_B SET DJ=A.DJ
FROM #TEMP_A A
WHERE #TEMP_B.CPBH =A.CPBH
AND A.RQ=(SELECT MAX(B.RQ)
FROM #TEMP_A B
WHERE #TEMP_B.RQ>=B.RQ AND A.CPBH=B.CPBH)
SELECT * FROM #TEMP_B
DROP TABLE #TEMP_A
DROP TABLE #TEMP_B
/*
结果
ID CPBH RQ DJ
----------- ---------- ----------------------- ---------------------
1 001 2011-01-05 00:00:00 1.00
2 001 2011-02-02 00:00:00 1.00
3 001 2011-05-01 00:00:00 2.00
4 001 2011-08-01 00:00:00 4.00
5 002 2011-01-05 00:00:00 1.00
6 002 2011-03-05 00:00:00 2.00
7 002 2011-05-05 00:00:00 3.00
8 002 2011-10-05 00:00:00 5.00
*/