34,838
社区成员




CREATE TABLE #T
(
id int
, roomid int
, hotelid int
, roomPreferentialPrice int
, companyCN varchar(20)
, address varchar(36)
)
insert into #T
select 9,3,9,210,'深圳皇龙酒店','深圳龙岗区爱联如意中路68号' union all
select 1992,7705,1992,248,'北京和平里宾馆','北京市东城区和平里兴化路化工大院4号' union all
select 1992,7706,1992,388,'北京和平里宾馆','北京市东城区和平里兴化路化工大院4号' union all
select 1992,7707,1992,398,'北京和平里宾馆','北京市东城区和平里兴化路化工大院4号' union all
select 9,19139,9,205,'深圳皇龙酒店','深圳龙岗区爱联如意中路68号' union all
select 9,19140,9,215,'深圳皇龙酒店','深圳龙岗区爱联如意中路68号' union all
select 6,20459,6,370,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 6,20460,6,390,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 6,20461,6,430,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 6,20463,6,618,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 1992,20564,1992,238,'北京和平里宾馆','北京市东城区和平里兴化路化工大院4号' union all
select 3,23678,3,100,'广州凯旋华美达大酒店','广州越秀区广州大道中明月一路九号'
select *
FROM #T
WHERE
1=1 and roomPreferentialPrice =(select min(roomPreferentialPrice) from #t where hotelid in (3,6,9,1992))
group by
hotelid
, id
, roomid
, roompreferentialprice
, companycn
, address
select * from #t t where hotelid in (3,6,9,1992) and roomPreferentialPrice
=(select min(roomPreferentialPrice) from #t where hotelid in (3,6,9,1992) and t.hotelid =hotelid)
结果:
id roomid hotelid roomPreferentialPrice companyCN address
----------- ----------- ----------- --------------------- -------------------- ------------------------------------
3 23678 3 100 广州凯旋华美达大酒店 广州越秀区广州大道中明月一路九号
6 20459 6 370 东莞凯莱酒店 东莞市长安镇358省新安路段牌坊旁548号
9 19139 9 205 深圳皇龙酒店 深圳龙岗区爱联如意中路68号
1992 20564 1992 238 北京和平里宾馆 北京市东城区和平里兴化路化工大院4号
(4 行受影响)
CREATE TABLE #T
(
id int
, roomid int
, hotelid int
, roomPreferentialPrice int
, companyCN varchar(20)
, address varchar(36)
)
insert into #T
select 9,3,9,210,'深圳皇龙酒店','深圳龙岗区爱联如意中路68号' union all
select 1992,7705,1992,248,'北京和平里宾馆','北京市东城区和平里兴化路化工大院4号' union all
select 1992,7706,1992,388,'北京和平里宾馆','北京市东城区和平里兴化路化工大院4号' union all
select 1992,7707,1992,398,'北京和平里宾馆','北京市东城区和平里兴化路化工大院4号' union all
select 9,19139,9,205,'深圳皇龙酒店','深圳龙岗区爱联如意中路68号' union all
select 9,19140,9,215,'深圳皇龙酒店','深圳龙岗区爱联如意中路68号' union all
select 6,20459,6,370,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 6,20460,6,390,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 6,20461,6,430,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 6,20463,6,618,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 1992,20564,1992,238,'北京和平里宾馆','北京市东城区和平里兴化路化工大院4号' union all
select 3,23678,3,100,'广州凯旋华美达大酒店','广州越秀区广州大道中明月一路九号'
select *
FROM #T
WHERE roomPreferentialPrice =(select min(roomPreferentialPrice) from #t where hotelid in (3,6,9,1992))
and hotelid in (3,6,9,1992)
结果:
id roomid hotelid roomPreferentialPrice companyCN address
----------- ----------- ----------- --------------------- -------------------- ------------------------------------
3 23678 3 100 广州凯旋华美达大酒店 广州越秀区广州大道中明月一路九号
(1 行受影响)
CREATE TABLE TABLE2
(
id int,roomid int,hotelid int,roomPreferentialPrice int,
companyCN varchar(20),address varchar(36)
)
insert into TABLE2
select 9,3,9,210,'深圳皇龙酒店','深圳龙岗区爱联如意中路68号' union all
select 1992,7705,1992,248,'北京和平里宾馆','北京市东城区和平里兴化路化工大院4号' union all
select 1992,7706,1992,388,'北京和平里宾馆','北京市东城区和平里兴化路化工大院4号' union all
select 1992,7707,1992,398,'北京和平里宾馆','北京市东城区和平里兴化路化工大院4号' union all
select 9,19139,9,205,'深圳皇龙酒店','深圳龙岗区爱联如意中路68号' union all
select 9,19140,9,215,'深圳皇龙酒店','深圳龙岗区爱联如意中路68号' union all
select 6,20459,6,370,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 6,20460,6,390,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 6,20461,6,430,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 6,20463,6,618,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 1992,20564,1992,238,'北京和平里宾馆','北京市东城区和平里兴化路化工大院4号' union all
select 3,23678,3,100,'广州凯旋华美达大酒店','广州越秀区广州大道中明月一路九号'
SELECT * FROM TABLE2 INNER JOIN
(
SELECT companyCN, MIN(roomPreferentialPrice) AS PRICE FROM TABLE2 GROUP BY companyCN )
T ON TABLE2.companyCN=T.companyCN AND TABLE2.roomPreferentialPrice=T.PRICE
/*
id roomid hotelid roomPreferentialPrice companyCN address companyCN PRICE
----------- ----------- ----------- --------------------- -------------------- ------------------------------------ -------------------- -----------
1992 20564 1992 238 北京和平里宾馆 北京市东城区和平里兴化路化工大院4号 北京和平里宾馆 238
6 20459 6 370 东莞凯莱酒店 东莞市长安镇358省新安路段牌坊旁548号 东莞凯莱酒店 370
3 23678 3 100 广州凯旋华美达大酒店 广州越秀区广州大道中明月一路九号 广州凯旋华美达大酒店 100
9 19139 9 205 深圳皇龙酒店 深圳龙岗区爱联如意中路68号 深圳皇龙酒店 205
(4 行受影响)
select t.* from reg_hotel t where hotelid in (3,6,9,1992) and roomPreferentialPrice = (select min(roomPreferentialPrice) from reg_hotel where hotelid in (3,6,9,1992) and hotelid = t.hotelid)
select t.* from reg_hotel t where hotelid in (3,6,9,1992) and not exists (select 1 from reg_hotel where hotelid in (3,6,9,1992) and hotelid = t.hotelid and roomPreferentialPrice < t.roomPreferentialPrice)
select [id],[companyCN],[address],minRoomPrice=dbo.F_GetHotelMinPrice([id]),
returnCash=dbo.F_GetReturnCash(roomid, '2012-4-9') ,roomid
from (
select rh.[id], v.[id] roomid, hotelid,roomPreferentialPrice,companyCN,address from V_HotelRooms v
left join reg_hotel rh on v.hotelid=rh.[id]
where rh.[id] in (9,1992,2090,2155)
and v.[id]=(select top 1 vr2.[id] from V_HotelRooms vr2 where vr2.hotelid=rh.[id] order by roomPreferentialPrice)
) t
select rh.[id], v.[id] roomid, hotelid,roomPreferentialPrice,companyCN,address from V_HotelRooms v
left join reg_hotel rh on v.hotelid=rh.[id]
where rh.[id] in (9,1992,2090,2155)
and v.[id]=(select top 1 vr2.[id] from V_HotelRooms vr2 where vr2.roomPreferentialPrice=
(select min(vr.roomPreferentialPrice) from V_HotelRooms vr where vr.hotelid=rh.[id]) and vr2.hotelid=rh.[id])
declare @T table
(
id int,roomid int,hotelid int,roomPreferentialPrice int,
companyCN varchar(20),address varchar(36)
)
insert into @T
select 9,3,9,210,'深圳皇龙酒店','深圳龙岗区爱联如意中路68号' union all
select 1992,7705,1992,248,'北京和平里宾馆','北京市东城区和平里兴化路化工大院4号' union all
select 1992,7706,1992,388,'北京和平里宾馆','北京市东城区和平里兴化路化工大院4号' union all
select 1992,7707,1992,398,'北京和平里宾馆','北京市东城区和平里兴化路化工大院4号' union all
select 9,19139,9,205,'深圳皇龙酒店','深圳龙岗区爱联如意中路68号' union all
select 9,19140,9,215,'深圳皇龙酒店','深圳龙岗区爱联如意中路68号' union all
select 6,20459,6,370,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 6,20460,6,390,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 6,20461,6,430,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 6,20463,6,618,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 1992,20564,1992,238,'北京和平里宾馆','北京市东城区和平里兴化路化工大院4号' union all
select 3,23678,3,100,'广州凯旋华美达大酒店','广州越秀区广州大道中明月一路九号'
select * from @T t
WHERE roomPreferentialPrice=(SELECT MIN(roomPreferentialPrice)
FROM @T WHERE hotelid=t.hotelid)
/*
id roomid hotelid roomPreferentialPrice companyCN address
----------- ----------- ----------- --------------------- -------------------- ------------------------------------
3 23678 3 100 广州凯旋华美达大酒店 广州越秀区广州大道中明月一路九号
6 20459 6 370 东莞凯莱酒店 东莞市长安镇358省新安路段牌坊旁548号
9 19139 9 205 深圳皇龙酒店 深圳龙岗区爱联如意中路68号
1992 20564 1992 238 北京和平里宾馆 北京市东城区和平里兴化路化工大院4号
*/
--roomPreferentialPrice 应该是 V_HotelRooms 表的吧
--try
select rh.[id], v.[id] roomid, hotelid,roomPreferentialPrice,companyCN,address from V_HotelRooms v
left join reg_hotel rh on v.hotelid=rh.[id] where hotelid in (3,6,9,1992)
and not exists(select 1 from V_HotelRooms where roomid =v.roomid and roomPreferentialPrice<v.roomPreferentialPrice)