Oracle RDBMS技术问询：查询各分支最高COST_MAINT房间，能否用子查询替代自连接？

当然不是只能用自连接啦！子查询完全可以搞定这个需求，而且还有几种不同的灵活写法，我结合你的数据给你详细说明：

方法1：关联子查询直接筛选 #

这种写法最直观，通过关联子查询找到每个分支的最高维护成本，再匹配对应的房间记录：

SELECT ROOM_NO, BRANCH_ID, COST_MAINT
FROM Maintenance_Record mr
WHERE COST_MAINT = (
    -- 子查询：获取当前分支的最大维护成本
    SELECT MAX(COST_MAINT)
    FROM Maintenance_Record
    WHERE BRANCH_ID = mr.BRANCH_ID
);

效果说明：如果同一个分支有多个房间的维护成本都是最大值，这个查询会返回所有符合条件的记录；如果只需要单条结果，可以额外添加排序或限制条件。针对你提供的数据，它会精准返回预期的报表结果。

方法2：窗口函数+子查询（Oracle 12c+推荐写法） #

利用Oracle支持的窗口函数，可以更简洁地实现需求，内层是一个子查询用于标记排序：

SELECT ROOM_NO, BRANCH_ID, COST_MAINT
FROM (
    -- 子查询：给每个分支的记录按维护成本降序排名
    SELECT 
        ROOM_NO, 
        BRANCH_ID, 
        COST_MAINT,
        ROW_NUMBER() OVER (PARTITION BY BRANCH_ID ORDER BY COST_MAINT DESC) AS rank_num
    FROM Maintenance_Record
) ranked_records
WHERE rank_num = 1;

细节调整：

• 用ROW_NUMBER()会给每个分支的最高成本记录分配排名1，若有多个同成本记录，只会返回其中一条（随机）；
• 如果需要返回所有同最高成本的房间，可以替换为RANK()或DENSE_RANK()函数。

方法3：分组子查询+JOIN #

先通过子查询分组得到每个分支的最高成本，再和原表连接匹配对应房间：

SELECT mr.ROOM_NO, mr.BRANCH_ID, mr.COST_MAINT
FROM Maintenance_Record mr
JOIN (
    -- 子查询：分组计算每个分支的最大维护成本
    SELECT BRANCH_ID, MAX(COST_MAINT) AS max_cost
    FROM Maintenance_Record
    GROUP BY BRANCH_ID
) branch_max_costs 
ON mr.BRANCH_ID = branch_max_costs.BRANCH_ID 
AND mr.COST_MAINT = branch_max_costs.max_cost;

效果说明：和第一种关联子查询逻辑类似，会返回所有符合分支最高成本的房间记录，适配你的数据需求完全没问题。

总结一下：自连接只是实现该需求的其中一种方式，子查询的写法反而更灵活、易读，上面几种方法都能完美达成你的报表目标。

内容的提问来源于stack exchange，提问作者HengHeng123