求一条sql语句，实现分组取最大值

Question

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) 
首先查询出来的结果如图：
id      roomid  hotelid roo…   companyCN       address 
9       3 9 210 深圳皇龙酒店 深圳龙岗区爱联如意中路68号
1992    7705 1992 248 北京和平里宾馆 北京市东城区和平里兴化路化工大院4号
1992    7706 1992 388 北京和平里宾馆 北京市东城区和平里兴化路化工大院4号
1992    7707 1992 398 北京和平里宾馆 北京市东城区和平里兴化路化工大院4号
9       19139 9 205 深圳皇龙酒店 深圳龙岗区爱联如意中路68号
9       19140 9 215 深圳皇龙酒店 深圳龙岗区爱联如意中路68号
6       20459 6 370 东莞凯莱酒店 东莞市长安镇358省新安路段牌坊旁548号
6       20460 6 390 东莞凯莱酒店 东莞市长安镇358省新安路段牌坊旁548号
6       20461 6 430 东莞凯莱酒店 东莞市长安镇358省新安路段牌坊旁548号
6       20463 6 618 东莞凯莱酒店 东莞市长安镇358省新安路段牌坊旁548号
1992    20564 1992 238 北京和平里宾馆 北京市东城区和平里兴化路化工大院4号
3       23678 3 100 广州凯旋华美达大酒店 广州越秀区广州大道中明月一路九号
 我需要的结果是每一家酒店中房价最低的房间信息，也就是根据hotelid分组，取roomPreferentialPrice最小的这一个数据，注意 hoteli in（）这个条件。还有就是现在是用的是sql2000版本……

Answer 1

select hotelid,min(roomPreferentialPrice) as price from tbl
--获取每个九点的最便宜的房价

Answer 2

select * from room  a where price=
(select hotelid,min(roomPreferentialPrice) as price from tbl b
 a.hotelid=b.hotelid)获取每个酒店的最低房价的房间的信息

Answer 3

--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)

Answer 4

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号
*/

Answer 5

表 V_HotelRooms、reg_hotel的结构。 

Answer 6

第二个语句你确定不会出现语法错误？ price=后面查询出来的是多列

Answer 7

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])

Answer 8

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

Answer 9

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)

Answer 10

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 行受影响)

Answer 11

那如果出现一家酒店的多个房型的价格都是最低的呢？如果是你上面说的这种情况就在group by 加一个条件吧，就可以了。

Answer 12

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 6,20464,6,370,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 6,20465,6,370,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 6,20466,6,370,'东莞凯莱酒店','东莞市长安镇358省新安路段牌坊旁548号' union all
select 1992,20564,1992,238,'北京和平里宾馆','北京市东城区和平里兴化路化工大院4号' union all
select 3,23678,3,100,'广州凯旋华美达大酒店','广州越秀区广州大道中明月一路九号'SELECT * FROM TABLE2 left join 
(
SELECT companyCN, MIN(roomPreferentialPrice) AS PRICE  FROM TABLE2 GROUP BY companyCN )
T ON  TABLE2.companyCN=T.companyCN AND TABLE2.roomPreferentialPrice=T.PRICE
where TABLE2.companyCN=T.companyCN AND TABLE2.roomPreferentialPrice=T.PRICE1992 20564 1992 238 北京和平里宾馆 北京市东城区和平里兴化路化工大院4号 北京和平里宾馆
6 20459 6 370 东莞凯莱酒店 东莞市长安镇358省新安路段牌坊旁548号 东莞凯莱酒店
6 20464 6 370 东莞凯莱酒店 东莞市长安镇358省新安路段牌坊旁548号 东莞凯莱酒店
6 20465 6 370 东莞凯莱酒店 东莞市长安镇358省新安路段牌坊旁548号 东莞凯莱酒店
6 20466 6 370 东莞凯莱酒店 东莞市长安镇358省新安路段牌坊旁548号 东莞凯莱酒店
3 23678 3 100 广州凯旋华美达大酒店 广州越秀区广州大道中明月一路九号 广州凯旋华美达大酒店
9 19139 9 205 深圳皇龙酒店 深圳龙岗区爱联如意中路68号 深圳皇龙酒店

Answer 13

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 行受影响)

Answer 14

不好意思，题目看错了;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 行受影响)