如何用SQL取得如下结果？
A表：
ID Supp_ID MCode
----------- ----------- -------------
1 118 301010101
2 118 302010101
3 118 306020216
B表：
ID MtSuppPriceM_ID Factory_ID Price EffectDate
1 1 1 5.5150 2001-07-25 00:00:00.000
2 1 2 5.5150 2001-07-25 00:00:00.000
3 2 1 5.5150 2001-08-25 00:00:00.000
4 2 2 5.5150 2001-08-25 00:00:00.000
5 3 1 5.5150 2001-09-25 00:00:00.000
6 3 2 5.5150 2001-09-25 00:00:00.000
7 1 1 5.5050 2001-08-01 00:00:00.000
8 1 2 5.5050 2001-08-01 00:00:00.000
9 2 1 5.5050 2001-09-05 00:00:00.000
10 2 2 5.5050 2001-09-05 00:00:00.000
11 3 1 5.5050 2001-10-01 00:00:00.000
12 3 2 5.5050 2001-10-01 00:00:00.000
期望结果集：
Supp_ID MCode Factory_ID Price EffectDate
------- --------- ----------- ----------- ----------------
118 306020216 1 5.5050 2001-10-01 00:00:00.000
118 306020216 2 5.5050 2001-10-01 00:00:00.000
118 302010101 1 5.5050 2001-09-05 00:00:00.000
118 302010101 2 5.5050 2001-09-05 00:00:00.000
118 301010101 1 5.5050 2001-08-01 00:00:00.000
118 301010101 2 5.5050 2001-08-01 00:00:00.000
即：若当前日期为：2001-11-1；则应取出如上的物料当前生效价格
TKS
问题点数：20、回复次数：5Top
1 楼icevi（按钮工厂）回复于 2001-10-16 09:26:12 得分 20
select a.supp_id,a.mcode,b.factory_id,b.price.b.EffectDate
from a,b
where a.id=b.MtSuppPriceM_ID and b.EffectDate<'2001-11-1'
and b.EffectDate =
(select max(EffectDate) as EffectDate
from b t1
where t1.MtSuppPriceM_ID =a.id and b.factory_id=t1.factory_id
and t1.MtSuppPriceM_ID <'2001-11-1')
Top
2 楼Robin_Fang（Robin）回复于 2001-10-16 09:34:53 得分 0
To:ICevi Tks
不过你的代码中有几个笔误，更改如下：
Select A.Supp_ID,A.MCode,B.Factory_ID,B.Price,B.EffectDate
From MtSPM A,MtSPD B
Where A.ID=B.MtSuppPriceM_ID And B.EffectDate<='2001-11-1'
And B.EffectDate = (Select Max(EffectDate) As EffectDate
From MtSPD T1
Where T1.MtSuppPriceM_ID =A.ID And B.Factory_ID=T1.Factory_ID
And T1.EffectDate <='2001-11-1')
稍后给分你，请查收
Top
3 楼pxq（风轻轻地吹）回复于 2001-10-16 09:43:27 得分 0
我觉得如果B表的主键ID字段如果和EffectDate时间是升函数，就是ID越大，EffectDate时间越新。可以取ID，应该快一些
select a.supp_id,a.mcode,b.factory_id,b.price.b.EffectDate
from b left join a on a.id=b.MtSuppPriceM_ID
where b.ID in (select max(ID) from b where EffertDate <'2001-11-1' group by b.MtSuppPrivceM_ID ,b.factory_id)Top
4 楼Robin_Fang（Robin）回复于 2001-10-16 10:37:13 得分 0
To pxq:
我觉得你说的有道理，且热心助人。准备给你一点分，聊表心意，却不能在此给出，是否还有其它方式可以给分？
TksTop
5 楼pxq（风轻轻地吹）回复于 2001-10-16 22:59:37 得分 0
呵呵，不客气Top
