CREATE TABLE TestA (OrderDate DATETIME,OrderNumber INT ,Product CHAR(10),CurrentPrice MONEY)
INSERT TestA SELECT '2008-05-06',111101,'a',100
UNION ALL SELECT '2008-05-08',111102,'a',100
UNION ALL SELECT '2008-02-06',111103,'a',100
UNION ALL SELECT '2008-01-31',111104,'a',100
UNION ALL SELECT '2008-05-01',111104,'b',120 --Test B :(价格调整表)
CREATE TABLE TestB (Product CHAR(10),AdjustDate DATETIME,OriginalPrice MONEY,NewPrice MONEY)
INSERT TestB SELECT 'a','2008-02-07',200,100
UNION ALL SELECT 'a','2008-02-01',150,200 */
go
select orderdate,ordernumber,product,(case when newprice is null then currentprice else newprice end) as price
from(
select t1.orderdate,t1.ordernumber,t1.product,t1.currentprice,t2.newprice
from testA t1 left join (
select a.product,b.maxd,b.mind,a.newprice from testB a inner join (
select product,max(adjustdate)as maxd,min(adjustdate)as mind from testB group by product) B
on a.product=b.product where a.adjustdate=b.mind) t2
on t1.product=t2.product and t1.orderdate < t2.maxd and t1.orderdate > t2.mind
)t
/*
orderdate ordernumber product price
----------------------- ----------- ---------- ---------------------
2008-05-06 00:00:00.000 111101 a 100.00
2008-05-08 00:00:00.000 111102 a 100.00
2008-02-06 00:00:00.000 111103 a 200.00
2008-01-31 00:00:00.000 111104 a 100.00
2008-05-01 00:00:00.000 111104 b 120.00(5 行受影响)*/
INSERT TestA SELECT '2008-05-06',111101,'a',100
UNION ALL SELECT '2008-05-08',111102,'a',100
UNION ALL SELECT '2008-02-06',111103,'a',100
UNION ALL SELECT '2008-01-31',111104,'a',100
UNION ALL SELECT '2008-05-01',111104,'b',120 --Test B :(价格调整表)
CREATE TABLE TestB (Product CHAR(10),AdjustDate DATETIME,OriginalPrice MONEY,NewPrice MONEY)
INSERT TestB SELECT 'a','2008-02-07',200,100
UNION ALL SELECT 'a','2008-02-01',150,200 */
go
select orderdate,ordernumber,product,(case when newprice is null then currentprice else newprice end) as price
from(
select t1.orderdate,t1.ordernumber,t1.product,t1.currentprice,t2.newprice
from testA t1 left join (
select a.product,b.maxd,b.mind,a.newprice from testB a inner join (
select product,max(adjustdate)as maxd,min(adjustdate)as mind from testB group by product) B
on a.product=b.product where a.adjustdate=b.mind) t2
on t1.product=t2.product and t1.orderdate < t2.maxd and t1.orderdate > t2.mind
)t
/*
orderdate ordernumber product price
----------------------- ----------- ---------- ---------------------
2008-05-06 00:00:00.000 111101 a 100.00
2008-05-08 00:00:00.000 111102 a 100.00
2008-02-06 00:00:00.000 111103 a 200.00
2008-01-31 00:00:00.000 111104 a 100.00
2008-05-01 00:00:00.000 111104 b 120.00(5 行受影响)*/
INSERT TestA SELECT '2008-05-06',111101,'a',100
UNION ALL SELECT '2008-05-08',111102,'a',100
UNION ALL SELECT '2008-02-06',111103,'a',100
UNION ALL SELECT '2008-01-31',111104,'a',100
UNION ALL SELECT '2008-05-01',111104,'b',120 CREATE TABLE TestB (Product CHAR(10),AdjustDate DATETIME,OriginalPrice MONEY,NewPrice MONEY)
INSERT TestB SELECT 'a','2008-02-07',200,100
UNION ALL SELECT 'a','2008-02-01',150,200 select orderdate,ordernumber,product,
price=case when exists(select 1 from testb where product=a.product and AdjustDate>=a.OrderDate)
then (select OriginalPrice from testb where product=a.product and AdjustDate=
(select top 1 AdjustDate from testb where product=a.product and AdjustDate>=a.OrderDate order by AdjustDate))
else CurrentPrice end
from testa a
INSERT TestA SELECT '2008-05-06',111101,'a',100
UNION ALL SELECT '2008-05-08',111102,'a',100
UNION ALL SELECT '2008-02-06',111103,'a',100
UNION ALL SELECT '2008-01-31',111104,'a',100
UNION ALL SELECT '2008-05-01',111104,'b',120
select * from TestA
CREATE TABLE TestB (Product CHAR(10),AdjustDate DATETIME,OriginalPrice MONEY,NewPrice MONEY)
INSERT TestB SELECT 'a','2008-02-07',200,100
UNION ALL SELECT 'a','2008-02-01',150,200
select * from TestBif exists(select * from TestA where Product in(select Product from TestB))
select A.OrderDate,A.Product,A.OrderNumber,B.NewPrice from TestA A left join TestB B
on A.Product=B.Product
else
select * from A
现在价格调整表只有两条数据,如果数据多了就麻烦了,那日期就不是maxDate和minDate两个了
那就要比对多个日期了,貌似要用游标或者临时表来弄了
好强悍,学习
哎,总觉得掌握不到SQL解决问题的思路
INSERT TestA SELECT '2008-05-06',111101,'a',100
UNION ALL SELECT '2008-05-08',111102,'a',100
UNION ALL SELECT '2008-02-06',111103,'a',100
UNION ALL SELECT '2008-01-31',111104,'a',100
UNION ALL SELECT '2008-05-01',111104,'b',120 --Test B :(价格调整表)
CREATE TABLE TestB (Product CHAR(10),AdjustDate DATETIME,OriginalPrice MONEY,NewPrice MONEY)
INSERT TestB SELECT 'a','2008-02-07',200,100
UNION ALL SELECT 'a','2008-02-01',150,200
UNION ALL SELECT 'b','2008-06-01',150,200
UNION ALL SELECT 'b','2008-04-28',150,200
go
select orderdate,ordernumber,product,(case when newprice is null then currentprice else newprice end) as price
from(
select t1.orderdate,t1.ordernumber,t1.product,t1.currentprice,t2.newprice
from testA t1 left join (
select a.product,b.maxd,b.mind,a.newprice from testB a inner join (
select product,max(adjustdate)as maxd,min(adjustdate)as mind from testB group by product) B
on a.product=b.product where a.adjustdate=b.mind) t2
on t1.product=t2.product and t1.orderdate < t2.maxd and t1.orderdate > t2.mind
)t
go
drop table testA,TestB
/*
orderdate ordernumber product price
----------------------- ----------- ---------- ---------------------
2008-05-06 00:00:00.000 111101 a 100.00
2008-05-08 00:00:00.000 111102 a 100.00
2008-02-06 00:00:00.000 111103 a 200.00
2008-01-31 00:00:00.000 111104 a 100.00
2008-05-01 00:00:00.000 111104 b 200.00(5 行受影响)
*/