时间戳

首页 > 解决方案 > 如何在 SQL 中仅提取第一张收据的数量

sql - 如何在 SQL 中仅提取第一张收据的数量

问题描述

我正在尝试运行一个 SQL，我想在其中查看采购订单编号的第一个收据编号和第一个收据编号的接收数量。

我的 SQL 提取了第一个收据号码，但我没有得到第一个收据号码的数量，而是我得到了购买号码的总接收数量

这是我的 SQL：

Select
  a.Purchase_Order_Number,
  min(b.Purchase_Receipt_Number) PO_Receipt_NUMBER,  
  min(c.Full_Date) Received_Date,
  sum(b.TRANSACTION_RECEIVED_ITEM_QUANTITY)  PO_Receive_Qty
from f_Purchase_Order    a
join f_Purchase_Receipt b
  on a.Purchase_Order_Key = b.Purchase_Order_Key
join d_Date    c
  on (b.Received_Date_Key = c.Date_Key)
where a.Purchase_Order_Number = '870892'
group by 
  a.Purchase_Order_Number

标签： sqlsnowflake-cloud-data-platform

解决方案

如果您想要第一行，请不要使用GROUP BY. ORDER BY和LIMIT：

Select po.Purchase_Order_Number,
       pr.Purchase_Receipt_Number,  
       d.Full_Date as Received_Date,
       pr.TRANSACTION_RECEIVED_ITEM_QUANTITY as PO_Receive_Qty
from f_Purchase_Order po join
     f_Purchase_Receipt pr
     on po.Purchase_Order_Key = pr.Purchase_Order_Key join
     d_Date d
     on pr.Received_Date_Key = d.Date_Key
where pr.Purchase_Order_Number = '870892'
order by d.Date_Key
limit 1;

请注意，我还修复了表别名，以便它们识别它们来自的表。

编辑：

如果你想要这一切Purchase_Order_Numbers，你会使用qualify：

Select po.Purchase_Order_Number,
       pr.Purchase_Receipt_Number,  
       d.Full_Date as Received_Date,
       pr.TRANSACTION_RECEIVED_ITEM_QUANTITY as PO_Receive_Qty
from f_Purchase_Order po join
     f_Purchase_Receipt pr
     on po.Purchase_Order_Key = pr.Purchase_Order_Key join
     d_Date d
     on pr.Received_Date_Key = d.Date_Key
order by d.Date_Key
qualify row_number() over (partition by Purchase_Order_Key order d.Date_Key) = 1;

推荐阅读