34,590
社区成员
发帖
与我相关
我的任务
分享
select B.PMSRoomTypeCode,B.PMSRoomTypeName,B.RoomDate,B.price
,PriceRule=(select top 1 PriceRule from A
where A.PMSRoomTypeCode=B.PMSRoomTypeCode and A.EffectiveDate<=B.RoomDate order by A.EffectiveDate desc)
,PayAmount=(select top 1 PayAmount from A
where A.PMSRoomTypeCode=B.PMSRoomTypeCode and A.EffectiveDate<=B.RoomDate order by A.EffectiveDate desc)
,PriceRuleType = (select top 1 PriceRuleType from A
where A.PMSRoomTypeCode=B.PMSRoomTypeCode and A.EffectiveDate<=B.RoomDate order by A.EffectiveDate desc)
from B
select distinct B.PMSRoomTypeCode,B.RoomDate,B.price
,PriceRule=(select top 1 PriceRule from A
where A.RoomTypeCode=B.PMSRoomTypeCode and A.EffectiveDate<=B.RoomDate order by A.EffectiveDate desc)
,PayAmount=(select top 1 PayAmount from A
where A.RoomTypeCode=B.PMSRoomTypeCode and A.EffectiveDate<=B.RoomDate order by A.EffectiveDate desc)
,A.PriceRuleType
from A,B where A.RoomTypeCode=B.PMSRoomTypeCode
select b.PMSRoomTypeCode,b.PMSRoomTypeName,b.RoomDate,b.Price,
a.PriceRule,a.PayAmount,a.PriceRuleType
from B表 b
cross apply
(select top 1 * from A表 a
where b.PMSRoomTypeCode=a.RoomTypeCode
and cast(b.RoomDate as date)>=cast(a.EffectiveDate as date)
order by cast(a.EffectiveDate as date) desc) a
SELECT
b.*, t.PriceRule, t.PayAmount, t.PriceRuleType
FROM B表 b
OUTER APPLY
(
SELECT TOP(1) PriceRule, PayAmount, PriceRuleType
FROM A表 a
WHERE a.RoomTypeCode = b.PMSRoomTypeCode
AND a.EffectiveDate <= b.RoomDate
ORDER BY a.EffectiveDate DESC
) t
select B.PMSRoomTypeCode,B.PMSRoomTypeName,B.RoomDate,B.price
,PriceRule=(select top 1 PriceRule from A
where A.PMSRoomTypeCode=B.PMSRoomTypeCode and A.EffectiveDate<=B.RoomDate order by A.EffectiveDate desc)
,PayAmount=(select top 1 PayAmount from A
where A.PMSRoomTypeCode=B.PMSRoomTypeCode and A.EffectiveDate<=B.RoomDate order by A.EffectiveDate desc)
,B.PriceRuleType
from B