sql - DB2 SQL，计算准备发货的订单总额

Question

我正在尝试计算总价值或所有订单，其中我们拥有填写订单所需的所有库存物品。在下面的示例中，我只想选择订单 100 的总值，因为没有足够的库存来填充订单 200。

+-------+------+-------------+--------------+-------+
| Order | Item | Qty Ordered | Qty In Stock | Price |
+-------+------+-------------+--------------+-------+
|  100  |   A  |     10      |     25       |  1.00 |
+-------+------+-------------+--------------+-------+
|  100  |   B  |     15      |     50       |  2.00 |
+-------+------+-------------+--------------+-------+
|  100  |   C  |     30      |     75       |  3.00 |
+-------+------+-------------+--------------+-------+
|  200  |   A  |      5      |     25       |  1.00 |
+-------+------+-------------+--------------+-------+
|  200  |   B  |    100      |     50       |  2.00 | * Not enough stock to fill
+-------+------+-------------+--------------+-------+
|  200  |   C  |     35      |     75       |  3.00 |
+-------+------+-------------+--------------+-------+

标签： sqldb2

Answer 1

怎么样：

select o.id, sum(o.qty_ordered * o.price) as total_value
  from orders o
  where o.id not in (
    select id from orders where qty_ordered > qty_in_stock
  )
  group by o.id