Lets say we have a view/table hotel(hotel_n,hotel_name, room_n, price). I want to find the cheapest room. I tried group by room_n, but I want the hotels name (hotel_name) to be shown to the board without grouping it.
让我们说我们有一个视图/表酒店（hotel_n，hotel_name，room_n，价格）。我想找到最便宜的房间。我试着按room_n进行分组，但是我希望将酒店名称（hotel_name）显示给主板而不进行分组。
So as an amateur with sql(oracle 11g) I began with
所以作为一个业余的sql（oracle 11g）我开始
select hotel_n, room_n, min(price)
from hotel
group by room_n;
but it shows the error: ORA-00979: not a GROUP BY expression. I know I have to type group by room_n, hotel_n, but I want the hotel_n to be seen in the table that I make without grouping by it!
但它显示错误：ORA-00979：不是GROUP BY表达式。我知道我必须按room_n，hotel_n键入group，但是我想在我没有分组的表格中看到hotel_n！
Any ideas? thank you very much!
有任何想法吗？非常感谢你！
1 个解决方案
#1
0
Aggregate functions are useful to show, well, aggregate information per group of rows. If you want to get a specific row from a group of rows in relation to the other group members (e.g., the cheapest room per room_n), you'd probably need an analytic function, such as rank:
聚合函数可用于显示每组行的聚合信息。如果您想从一组行中获取与其他组成员相关的特定行（例如，每个room_n最便宜的房间），您可能需要一个分析函数，例如rank：
SELECT hotel_n, hotel_name, room_n, price
FROM (SELECT hotel_n, hotel_name, room_n, price
RANK() OVER (PARTITION BY room_n ORDER BY price ASC) rk
FROM hotel) t
WHERE rk = 1
#1
0
Aggregate functions are useful to show, well, aggregate information per group of rows. If you want to get a specific row from a group of rows in relation to the other group members (e.g., the cheapest room per room_n), you'd probably need an analytic function, such as rank:
聚合函数可用于显示每组行的聚合信息。如果您想从一组行中获取与其他组成员相关的特定行（例如，每个room_n最便宜的房间），您可能需要一个分析函数，例如rank：
SELECT hotel_n, hotel_name, room_n, price
FROM (SELECT hotel_n, hotel_name, room_n, price
RANK() OVER (PARTITION BY room_n ORDER BY price ASC) rk
FROM hotel) t
WHERE rk = 1