有两个表
一个就是<酒店表:jiudian> 包括 id title memo address 等字段还有一个是<酒店房间表:jiudian> 包括 id roomname price(房间价) fid(所属酒店id) 等字段
他们关系是一对多现在要查询出 全部酒店的信息 和 酒店房间的 最低价 该如何查询
select a.id as id,a.memo as memo,a.address as address, b.price as price from jiudian a left join jdroom b on b.fid=a.id 可是这样查询出来 一个酒店对应的全部价格,请大家帮忙。
一个就是<酒店表:jiudian> 包括 id title memo address 等字段还有一个是<酒店房间表:jiudian> 包括 id roomname price(房间价) fid(所属酒店id) 等字段
他们关系是一对多现在要查询出 全部酒店的信息 和 酒店房间的 最低价 该如何查询
select a.id as id,a.memo as memo,a.address as address, b.price as price from jiudian a left join jdroom b on b.fid=a.id 可是这样查询出来 一个酒店对应的全部价格,请大家帮忙。
select a.id id,min(b.price) price from jiudian a left join jdroom b on a.id=b.fid group by a.id这是含有酒店信息的同时又查出对应酒店的所有房间中最低的价格
select jiudian.id,jiudian.title,jiudian.memo,jiudian.address,test.price as price from jiudian,(select a.id id,min(b.price) price from jiudian a left join jdroom b on a.id=b.fid group by a.id)test where jiudian.id=test.id
select a.id as id,a.memo as memo,a.address as address, min(b.price) as price
from jiudian a left join jdroom b on b.fid=a.id
group by a.id as id,a.memo as memo,a.address as address如果数据库如oracle支持子查询像这样,效率要高点:
select a.id as id,a.memo as memo,a.address as address,
(select min(b.price) from jdroom b where b.fid=a.id) as price
from jiudian a差不多就这样。
select a.id id,min(b.price) price from jiudian a left join jdroom b on a.id=b.fid group by a.id这是含有酒店信息的同时又查出对应酒店的所有房间中最低的价格
select jiudian.id,jiudian.title,jiudian.memo,jiudian.address,test.price as price from jiudian,(select a.id id,min(b.price) price from jiudian a left join jdroom b on a.id=b.fid group by a.id)test where jiudian.id=test.id