SELECT h.id, h.name, r.id, r.roomtype, MIN(price) AS MMP FROM roomtype r left JOIN hotel h ON r.hotelID=h.id GROUP BY h.id, h.name, r.id, r.roomtype试试
解决方案 »
- ===========XML列使用Contatins查询的问题=========
- sql 连接查询的问题
- 一句sql帮忙看下什么意思,不要以为很简单!
- 菜鸟问题,如何取字符串中第n个指定字符的位置?
- 求教,一个关于SQL的问题
- 请教:如何将sqlserver和oracle中的数据相互复制
- sql条件查询求和求助
- 如果让数据库(SQL Server)一表中字段a值等于字段b值
- 学生选课模型设计:一个学生可以选择多门课程,而一门课程可以被多个学生选中...问..这个数据库模型应该怎样设计!!!!
- Oracle 8i的问题,请指教
- 打印数据库中的字段问题!
- 40分请叫一个sql 语句?在线等待!!!!!!
FROM hotel h left join(
select r.*
from roomtype r,(select price=min(price) from roomtype group by hotelid)r1
where r.price=r1.price
)b on r.hotelid=h.id
FROM hotel h left join(
select r.*
from roomtype r,(select price=min(price) from roomtype group by hotelid)r1
where r.price=r1.price
)r on r.hotelid=h.id
用LEFT OUTER JOIN 就OK
create table hotel(id int,name varchar(10))
insert hotel select 1,'金陵饭店'
union all select 2,'电子大厦'
union all select 3,'白宫大酒店'create table roomtype(id int,hotelid int,roomtype varchar(10),price int)
insert roomtype select 1,2,'标准间' ,125
union all select 2,2,'豪华套间',380
union all select 3,2,'普通间' ,80
union all select 4,1,'标准间' ,200
union all select 5,3,'标准间' ,160
go--查询
SELECT h.id, h.name, r.id, r.roomtype, r.price AS MMP
FROM hotel h left join(
select r.*
from roomtype r,(select price=min(price) from roomtype group by hotelid)r1
where r.price=r1.price
)r on r.hotelid=h.id
go--删除测试
drop table hotel,roomtype/*--测试结果id name id roomtype MMP
----------- ---------- ----------- ---------- -----------
1 金陵饭店 4 标准间 200
2 电子大厦 3 普通间 80
3 白宫大酒店 5 标准间 160(所影响的行数为 3 行)
--*/
SELECT h.id, h.name, r.id, r.roomtype, r.price AS MMP
FROM hotel h left join(
select r.*
from roomtype r,(
select id=min(id)
from roomtype r,(select price=min(price) from roomtype group by hotelid)r1
where r.price=r1.price
group by hotelid
)r1 where r.id=r1.id
)r on r.hotelid=h.id
insert into @tb1
select 1,'金陵饭店'union all
select 2,'电子大厦'union all
select 3,'白宫大酒店'declare @tb2 table(myid2 int,hotelid int,roomtype varchar(20) ,price int)
insert into @tb2
select 1,2,'标准间',125 union all
select 2,2,'豪华间',380 union all
select 3,2,'普通间',80 union all
select 4,1,'标准间',200 union all
select 5,3,'标准间',160select h.myid,h.myname,r3.roomtype,r3.price as mmp from @tb1 h left join (select r1.* from @tb2 r1 , (select price=min(price) from @tb2 group by hotelid)r2 where r1.price=r2.price)r3 on h.myid=r3.hotelid
显示数据每两个重复一次啊!