时间戳

首页 > 解决方案 > 如果有数据链接到订单号，我如何提取一些而不是另一个（不存在？）？

sql - 如果有数据链接到订单号，我如何提取一些而不是另一个（不存在？）？

问题描述

我正在使用 Toad for Oracle。

我目前在我们的院子和仓库中都有带有物品的订单号。我正在寻找只有院子里有物品的物品。

基本上我需要计算院子里不同物品的数量以及每个订单的重量总和

我目前有它，它计算院子里每个订单的物品，但它不限于只有院子里物品的订单

select ORD.WH_ID, SIO.ORDER_NUMBER, SIO.CUSTOMER_NAME, COUNT    (ORD.ITEM_NUMBER) AS LINE_COUNT, SUM(TIM.UNIT_WEIGHT * ORD.QTY) AS WEIGHT
from t_ORDER_DETAIL ORD
JOIN T_ITEM_MASTER TIM
ON ORD.WH_ID = TIM.WH_ID
AND ORD.ITEM_NUMBER = TIM.ITEM_NUMBER
JOIN SI_ORDER SIO
ON SIO.WH_ID = ORD.WH_ID
AND SIO.ORDER_NUMBER = ORD.ORDER_NUMBER
where ORD.wh_id = '603'
--AND ORDER_NUMBER = '1923421'
AND PROCESS_DATE >= SYSDATE -1
AND TIM.CLASS_ID IN ('GYARD','GSSPIPE','GBLKALLTHR','GCOPPPIPE','GCOPPCNC')
AND SIO.CARRIER = 'OT'
GROUP BY ORD.WH_ID, SIO.ORDER_NUMBER, SIO.CUSTOMER_NAME, WEIGHT
ORDER BY SIO.ORDER_NUMBER

table 1
order #   Item #
Order1         Y51
Order1         Y52
Order2         Y51
Order2         W35
Order3         W21
Order3         W80

Table 2
Item #       Location
Y51          Yard
Y52          Yard
W35          Warehouse
W21          Warehouse
W80          Warehouse

从上面的样本集中，我只想看到 Order1，因为它是唯一包含仅位于 Yard 中的项目的订单

标签： sqloracletoad

解决方案

我正在使用您的两个示例表（表 1 = 订单，表 2 = 项目）。这是如何找到包含堆场物品但没有仓库物品的订单的订单号：

select o.orderno
from orders o
join items i on i.itemno = o.itemno
group by o.orderno
having count(case when i.location = 'Yard' then 1 end) > 0
   and count(case when i.location = 'Warehouse' then 1 end) = 0;

如果要显示完整的订单，请在子查询中使用上述查询，例如：

select *
from orders
where orderno in ( <above query> )
order by orderno, itemno;

如果您想要每个订单的聚合，您可以相应地修改查询，例如：

select
  o.orderno,
  count(*) as number_of_positions,
  sum(o.amount) as number_of_pieces,
  sum(o.amount * i.weight) as total_weight
from orders o
join items i on i.itemno = o.itemno
group by o.orderno
having count(case when i.location = 'Yard' then 1 end) > 0
   and count(case when i.location = 'Warehouse' then 1 end) = 0
order by o.orderno;

如果你想要每个项目的聚合，你可能需要子查询加上一个单独的聚合。

推荐阅读