sql - 如何在 Postgres 中对多个表中出现的事件求和

Question

我有，比方说，2张 table

• UK_Order 和 UK_Order_Item
• F_Order 和 F_Order_Item

UK_Order(和 F_Order)具有以下字段:Id、orderStatus。UK_Order_Item(和 F_Order_Item)具有以下字段:orderId、model 和 order_quantity。

我正在尝试编写 SQL 以返回通过英国和 F 销售的不同型号的总数，但不能。到目前为止，我想出了:

SELECT model, SUM(order_quantity) AS quantityOrdered 
FROM uk_order_item 
WHERE orderId 
IN
(
    SELECT Id FROM uk_order WHERE orderStatus = 'Incomplete'
)
GROUP BY model

还有

SELECT model, SUM(order_quantity) AS quantitySold
FROM f_order_item 
WHERE orderId 
IN
(
    SELECT Id FROM f_order WHERE orderStatus = 'Incomplete'
)
GROUP BY model

返回例如:

Model, QuantityOrdered
Volkswagen, 3
Ford, 2
Citroen, 4
...

然后要找到在英国和 F 销售的大众汽车总数，我必须做一点计算(即 volkswagenCount = uk 的 volkwagen 的 quantitySold + F 的 volkwagen 的 quantitySold

换句话说，我的单独查询返回为英国和 F(rance) 销售的模型总数 - 我会为德国、西类牙等重复此查询。但是有没有办法返回每个模型的销售总数整个欧洲(英国、法国、德国等)？

Answer 1

数据库设计

我会重新考虑您的数据库设计。包括所有国家的统一表格可能是一个更好的主意。因此，您将拥有所有国家/地区的单一表格:

order_item 
order

只需在表 order 中添加一个 country_id。

更简单的查询

IN 结构不必要地复杂。更简单、更快速:

SELECT oi.model, sum(oi.order_quantity) AS quantity_ordered 
FROM   uk_order      o
JOIN   uk_order_item oi ON oi.orderid = o.id
WHERE  o.orderstatus = 'Incomplete'
GROUP  BY 1;

虽然您有单独的表格，但没有什么比将单独的计数相加更简单或更快的了:

SELECT model, sum(quantity_ordered) AS total_quantity_ordered
FROM  (
   SELECT model, sum(order_quantity) AS quantity_ordered
   FROM   uk_order      o
   JOIN   uk_order_item oi ON oi.orderid = o.id
   WHERE  o.orderstatus = 'Incomplete'
   GROUP  BY 1

   UNION  ALL
   SELECT model, sum(order_quantity) AS quantity_ordered
   FROM   f_order       o
   JOIN   f_order_item  oi ON oi.orderid = o.id
   WHERE  o.orderstatus = 'Incomplete'
   GROUP  BY 1
   ) sub
GROUP  BY 1;

您可以在求和之前使用 UNION ALL 将行合并到一个派生表中:

SELECT model, sum(quantity_ordered) AS total_quantity_ordered
FROM  (
   SELECT model, order_quantity AS quantity_ordered
   FROM   uk_order      o
   JOIN   uk_order_item oi ON oi.orderid = o.id
   WHERE  o.orderstatus = 'Incomplete'

   UNION  ALL
   SELECT model, order_quantity AS quantity_ordered
   FROM   f_order       o
   JOIN   f_order_item  oi ON oi.orderid = o.id
   WHERE  o.orderstatus = 'Incomplete'
   ) sub
GROUP  BY 1;

但我怀疑它会更快。

我的一贯建议是不要在 Postgres 中使用 CaMeL 大小写标识符，无论是否引用。让您的生活更轻松。