我有下面的图片和查询。问题是我的查询显示我错误的值,在多个记录中没有显示正确的hotelname,这意味着我想要VoucherHotelTable中的第一家酒店,但由于与众不同,它向我显示了第二家酒店,我认为请您帮我解决该问题
Select distinct v.VoucherId,u.Name,v.ArrivalFromCity,
CAST(v.ArrivalDate AS DATE) as ADDate, v.ArrivalFlightNo, hm.HotelName
from VoucherMaster v
inner join UserMaster u on v.AgentId = u.UserId
inner join VoucharHotel vh on v.VoucherId = vh.VoucharId
inner join HotelMaster hm on vh.HotelId = hm.HotelId
inner join AirportTerminal t on v.ArrivalTerminalId = t.AirprtTerminalId
where v.ArrivalDate between '11/15/2018 12:00:00 AM'
and '11/16/2018 12:00:00 AM' AND v.ArrivalSectorId = 3
此查询通过分区工作。尝试这个。rank()函数应为每个凭证返回酒店的等级。
;with VoucherHotelRnk as (
select voucherId, hotelId, date, rank() over(partition by voucherId order by date) rnk
from VoucharHotel
)
Select v.VoucherId,u.Name,v.ArrivalFromCity,
CAST(v.ArrivalDate AS DATE) as ADDate, v.ArrivalFlightNo, hm.HotelName
from VoucherMaster v
inner join UserMaster u on v.AgentId = u.UserId
inner join VoucherHotelRnk vh on v.VoucherId = vh.VoucherId and vh.rnk = 1
inner join HotelMaster hm on vh.HotelId = hm.HotelId
inner join AirportTerminal t on v.ArrivalTerminalId = t.AirprtTerminalId
where v.ArrivalDate between '11/15/2018 12:00:00 AM'
and '11/16/2018 12:00:00 AM' AND v.ArrivalSectorId = 3
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句