如何将两个Oracle SQL查询作为临时表连接并满足指定匹配条件？

通过WITH子句将两个查询定义为临时表，再按照指定条件进行连接，最终SQL如下：

WITH query1 AS (
    select 
        nomhotel, max(nombre) as mxnb 
    from 
        (select nomhotel, typechambre, count(typechambre) as nombre          
         from 
             (select h.nomhotel, c2.typechambre 
              from hotel h, reservation r, chambre c2 
              where h.ville = 'Alger' 
                and h.numhotel = c2.numhotel 
                and c2.numhotel = r.numhotel 
                and c2.numchambre = r.numchambre) 
         group by 
             nomhotel, typechambre) 
    group by 
        nomhotel
),
query2 AS (
    select 
        nomhotel, typechambre, count(typechambre) as nombre 
    from 
        (select 
             h.nomhotel, c2.typechambre 
         from
             hotel h, reservation r, chambre c2 
         where
             h.ville = 'Alger' 
             and h.numhotel = c2.numhotel 
             and c2.numhotel = r.numhotel 
             and c2.numchambre = r.numchambre) 
    group by 
        nomhotel, typechambre
)
SELECT q2.nomhotel, q2.typechambre, q2.nombre
FROM query1 q1
INNER JOIN query2 q2 
    ON q1.nomhotel = q2.nomhotel 
    AND q1.mxnb = q2.nombre;

说明 #

该语句会筛选出阿尔及尔（Alger）地区每间酒店中，预订量最高的房型及其对应的预订数量。

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