连接两个sql查询，两个原始查询作为临时表

Question

我有两个SQL (Oracle)查询

查询# 1:

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

查询# 2:

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

,

• query1。Nomhotel = query2。nomhotel,
• query1。mxnb = query2.count(typechambre)

Answer 1

如果你的查询工作正常，那么你所要做的就是在你的问题结束时也陈述的条件下加入它们。像这样:

Select
q1.columns, q2.columns, .....
From
(your Query1 in brackets) q1
Inner Join
(your Query2 in brackets) q2 ON(q2.nomhotel = q1.nomhotel And q2.nombre = q1.mxnb)

或者像这样使用公共表表达式(cte):

WITH 
q1 AS 
(your query1),
q2 AS
(your query2)
Select
q1.columns, q2.columns, .....
From
q1
Inner Join
q2 ON(q2.nomhotel = q1.nomhotel And q2.nombre = q1.mxnb)

问候……