每个城市十个最便宜的房间
我有 3 张桌子房间、酒店、城市,我如何选择十个最便宜的房间及其每个城市的 hotel_id 可以对每个城市执行子查询吗?
SELECT price, room_id, hotel_id, city_id
FROM Hotel
JOIN Room USING(hotel_id)
ORDER BY price
LIMIT 10
Scheme
CREATE TABLE `City` (
`city_id` int(11) NOT NULL AUTO_INCREMENT,
`city_name` varchar(50) NOT NULL,
PRIMARY KEY (`city_id`)
)
CREATE TABLE `Hotel` (
`hotel_id` int(11) NOT NULL AUTO_INCREMENT,
`city_id` int(11) NOT NULL,
`hotel_name` varchar(100) NOT NULL,
PRIMARY KEY (`hotel_id`),
CONSTRAINT `Hotel_FK_1` FOREIGN KEY (`city_id`) REFERENCES `City` (`city_id`) ON DELETE CASCADE ON UPDATE CASCADE
)
CREATE TABLE `Room` (
`room_id` int(11) NOT NULL AUTO_INCREMENT,
`hotel_id` int(11) NOT NULL,
`room_name` varchar(255) DEFAULT NULL,
`room_price1` int(11) DEFAULT NULL,
PRIMARY KEY (`room_id`),
CONSTRAINT `Room_FK_1` FOREIGN KEY (`hotel_id`) REFERENCES `Hotel` (`hotel_id`) ON DELETE CASCADE ON UPDATE CASCADE
)
使用子查询查询,
SELECT
r.room_price1, r.room_id, h.hotel_name, c.city_name
FROM Hotel h
INNER JOIN Room r ON
h.hotel_id = r.hotel_id
INNER JOIN City c USING(city_id)
WHERE (
SELECT count(*) from Room as rt
JOIN Hotel ht using(hotel_id)
where c.city_id = ht.city_id and rt.room_price1 < r.room_price1 OR (rt.room_price1 = r.room_price1 AND rt.room_id < r.room_id)
) < 3
ORDER BY c.city_name, h.hotel_id, r.room_price1
如果某些酒店有一些 room_price1,此查询返回超过 3 行的城市,我需要每个城市 3 个唯一的酒店,此查询可以从一家酒店返回 3 个房间,
我会尝试使用用户变量,
set @num := 0, @type := '';
select *
from (
select r.room_price1 pr, r.room_id, h.hotel_name, c.city_name,
@num := if(@type = city_id, @num + 1, 1) as row_number,
@type := city_id as dummy
from Hotel h
JOIN Room r USING(hotel_id)
INNER JOIN City c USING(city_id)
order by city_name, r.room_price1 asc
) as x where x.row_number <=3;
此方法返回 row_number < 的错误排序6(我的初始化数据)
I have 3 table Room, Hotel, City, how can i select ten cheapest room and its hotel_id for each city
possible to execute the subquery for each city?
SELECT price, room_id, hotel_id, city_id
FROM Hotel
JOIN Room USING(hotel_id)
ORDER BY price
LIMIT 10
Scheme
CREATE TABLE `City` (
`city_id` int(11) NOT NULL AUTO_INCREMENT,
`city_name` varchar(50) NOT NULL,
PRIMARY KEY (`city_id`)
)
CREATE TABLE `Hotel` (
`hotel_id` int(11) NOT NULL AUTO_INCREMENT,
`city_id` int(11) NOT NULL,
`hotel_name` varchar(100) NOT NULL,
PRIMARY KEY (`hotel_id`),
CONSTRAINT `Hotel_FK_1` FOREIGN KEY (`city_id`) REFERENCES `City` (`city_id`) ON DELETE CASCADE ON UPDATE CASCADE
)
CREATE TABLE `Room` (
`room_id` int(11) NOT NULL AUTO_INCREMENT,
`hotel_id` int(11) NOT NULL,
`room_name` varchar(255) DEFAULT NULL,
`room_price1` int(11) DEFAULT NULL,
PRIMARY KEY (`room_id`),
CONSTRAINT `Room_FK_1` FOREIGN KEY (`hotel_id`) REFERENCES `Hotel` (`hotel_id`) ON DELETE CASCADE ON UPDATE CASCADE
)
query with subquery
SELECT
r.room_price1, r.room_id, h.hotel_name, c.city_name
FROM Hotel h
INNER JOIN Room r ON
h.hotel_id = r.hotel_id
INNER JOIN City c USING(city_id)
WHERE (
SELECT count(*) from Room as rt
JOIN Hotel ht using(hotel_id)
where c.city_id = ht.city_id and rt.room_price1 < r.room_price1 OR (rt.room_price1 = r.room_price1 AND rt.room_id < r.room_id)
) < 3
ORDER BY c.city_name, h.hotel_id, r.room_price1
if some hotels have some room_price1 this query return more then 3 row for city and i need 3 unique hotel per city this query can return 3 room from one hotel
ill try use user vars
set @num := 0, @type := '';
select *
from (
select r.room_price1 pr, r.room_id, h.hotel_name, c.city_name,
@num := if(@type = city_id, @num + 1, 1) as row_number,
@type := city_id as dummy
from Hotel h
JOIN Room r USING(hotel_id)
INNER JOIN City c USING(city_id)
order by city_name, r.room_price1 asc
) as x where x.row_number <=3;
this method return wrong sort for row_number < 6 (my init data)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
关于这个主题有很多不同的博客文章。
对我来说最有价值的可能是
Xaprb 帖子(有关详细说明,请参阅“从每组中选择前 N 行”部分)
http://www.xaprb.com/blog/2006/12/07/how-to- select-the-firstleastmax-row-per-group-in-sql/
在你的情况下这意味着。根据您的需要对此 SQL 进行建模。
让我们尝试一下
,您可以看到您进行了一个子查询来选择所有房间的数量
其价格低于当前根,看看是否
计数小于或等于 10。
特别注意我如何更改
为
,因为在 Xaprb 的示例中,他选择了 N 个最便宜的水果
希望有帮助。
顺便说一句,Stackoverflow 不是用来解决作业的。你也应该自己学点东西;-)
there a ton of different blog post about this topic out there.
The most valuable for me, possibly was
Xaprb post (see section "Select the top N rows from each group" for a detailed explaination)
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
In your case that would mean. Modelling this SQL to your needs.
lets try it out
as you can see you make an subquery to select the count of all rooms
that have a price that is lower than the current root and see if
the count is less than or equal to 10.
Observe in particular how i changed the
to
because in the example of Xaprb he selects the N cheapest fruits
Hope that helps.
BTW Stackoverflow isn't for solving homework. You should learn something your self as well ;-)
我假设表
Room
引用了它所属的Hotel
I am assuming the table
Room
has a reference to theHotel
it belongs to