sql - 如何构建一个 sql 查询以返回 avg(price)、min(price)、max(price) 与 avg(order)、min(order)、max(order)

Question

我想创建一个 sql 查询，为 2 个不同的查询一起返回结果。例如，我想要以下形式的结果:产品名称, avg(price), min(price), max(price), avg(order), min(order), max(order)目前我有 2 个形式的 sql 查询:

select 
  product.name, order.id, avg(price), min(price), max(price) 
from 
  product, order 
where
  product.name = order.product_name and product.name = 'price' 
group by 
  product.name, order.id

select 
  product.name, order.id, avg(order), min(order), max(order) 
from 
  product, order 
where 
  product.name = order.product_name and product.name = 'order' 
group by 
  product.name, order.id

有些产品有价格和订单，有些只有价格，有些只有和订单。我如何编写一个查询来显示所有结果并加入那些同时具有订单和价格的结果并显示同时具有订单和价格的结果？

Answer 1

我认为您需要的是完全外部联接，因为行可以在一个表中或另一个表中或同时在两个表中:

SELECT 
   NVL(t1.name,t2.name) as name,
   NVL(t1.id, t2.id) as id,
   avg_price,
   min_price,
   max_price,
   avg_order,
   min_order,
   max_order
FROM 
(select product.name, order.id, avg(price) as avg_price, min(price) as min_price, max(price) as max_price 
from product, order 
where product.name = order.product_name and product.name = 'price' 
group by product.name, order.id) t1
FULL OUTER JOIN
(select product.name, order.id, avg(order) as avg_order, min(order) as min_order, max(order) as max_order
from product, order 
where product.name = order.product_name and product.name = 'order' 
group by product.name, order.id) t2
ON t1.name = t2.name