sql - 仅选择截止日期最早的记录
问题描述
我尝试了以下查询,该查询返回每个 STOCKCODE 的所有行,因为我只在记录最早的 PurchOrd_Lines.Due Date 之后
SELECT
PURCHORD_LINES.STOCKCODE,
STOCK_ITEMS.DESCRIPTION,
(X_FREE_STOCK_VW.PhysicalQty - X_FREE_STOCK_VW.CommittedQty) AS 'FREE STOCK',
X_FREE_STOCK_VW.PhysicalQty AS 'On Hand',
X_FREE_STOCK_VW.CommittedQty AS 'Committed',
X_FREE_STOCK_VW.IncommingQty AS 'Purch_Ord',
PURCHORD_LINES.HDR_SEQNO,
(PURCHORD_LINES.ORD_QUANT-PURCHORD_LINES.SUP_QUANT) AS 'QTY',
CONVERT(DATETIME, MIN(PURCHORD_LINES.DUEDATE), 103) AS 'ETA',
PURCHORD_HDR.X_PURCHASEORDERREF AS 'Reference'
FROM
PURCHORD_LINES PURCHORD_LINES
JOIN
STOCK_ITEMS ON STOCK_ITEMS.STOCKCODE = PURCHORD_LINES.STOCKCODE
JOIN
X_FREE_STOCK_VW ON X_FREE_STOCK_VW.STOCKCODE = PURCHORD_LINES.STOCKCODE
JOIN
PURCHORD_HDR ON PURCHORD_HDR.SEQNO = PURCHORD_LINES.HDR_SEQNO
WHERE
X_FREE_STOCK_VW.LOCATION = 1
AND STOCK_ITEMS.STOCK_CLASSIFICATION IN (0,10,20,200,210,220)
AND STOCK_ITEMS.STOCKCODE NOT LIKE '%2' AND STOCK_ITEMS.STOCKCODE NOT LIKE '%3'
AND (X_FREE_STOCK_VW.PhysicalQty - X_FREE_STOCK_VW.CommittedQty) <= 0
AND X_FREE_STOCK_VW.CommittedQty > 0
AND LEN(STOCK_ITEMS.STOCKCODE) > 6
AND STOCK_ITEMS.DESCRIPTION IS NOT NULL
AND STOCK_ITEMS.DESCRIPTION != ''
AND STOCK_ITEMS.SUPPLIERNO IN (1009, 1024, 1068, 1115, 1146, 1170, 1259, 1306, 1410, 2768)
AND STOCK_ITEMS.X_BUYER = 'P'
AND ((PURCHORD_LINES.ORD_QUANT - PURCHORD_LINES.SUP_QUANT) > 0 AND
(PURCHORD_LINES.SUP_QUANT/PURCHORD_LINES.ORD_QUANT) < 0.9)
AND ((PURCHORD_HDR.STATUS <> 2)
AND (PURCHORD_HDR.X_DELIVERYSTATUS <> 4)
AND (PURCHORD_LINES.ORD_QUANT > 0))
GROUP BY
PURCHORD_LINES.STOCKCODE, STOCK_ITEMS.DESCRIPTION,
X_FREE_STOCK_VW.PhysicalQty, X_FREE_STOCK_VW.CommittedQty,
X_FREE_STOCK_VW.IncommingQty, PURCHORD_LINES.HDR_SEQNO,
PURCHORD_LINES.ORD_QUANT, PURCHORD_LINES.SUP_QUANT,
PURCHORD_HDR.X_PURCHASEORDERREF
输出是
STOCKCODE| FREE STOCK |On Hand |Committed|Purch_Ord|HDR_SEQNO|QTY|ETA |Reference
42165 | -351 |-29 |322 |406 |106200 |84 |21/02/2020 |426/19
42165 | -351 |-29 |322 |406 |107052 |87 |20/03/2020 |454/19
42165 | -351 |-29 |322 |406 |107626 |150|11/04/2020 |024/20
42166 | -15 |41 |57 |406 |107626 |150|13/02/2020 |074/20
42166 | -15 |41 |57 |406 |107626 |150|17/02/2020 |089/20
而我只需要它为每个股票代码返回最早日期(ETA)的行。做错了什么?
解决方案
您可以利用 row_number 来实现这一点
with data
as ( /*The original query*/
SELECT
PURCHORD_LINES.STOCKCODE,
STOCK_ITEMS.DESCRIPTION,
(X_FREE_STOCK_VW.PhysicalQty - X_FREE_STOCK_VW.CommittedQty) AS 'FREE STOCK',
X_FREE_STOCK_VW.PhysicalQty AS 'On Hand',
X_FREE_STOCK_VW.CommittedQty AS 'Committed',
X_FREE_STOCK_VW.IncommingQty AS 'Purch_Ord',
PURCHORD_LINES.HDR_SEQNO,
(PURCHORD_LINES.ORD_QUANT-PURCHORD_LINES.SUP_QUANT) AS 'QTY',
CONVERT(DATETIME, MIN(PURCHORD_LINES.DUEDATE), 103) AS 'ETA',
PURCHORD_HDR.X_PURCHASEORDERREF AS 'Reference'
FROM PURCHORD_LINES PURCHORD_LINES
JOIN STOCK_ITEMS ON STOCK_ITEMS.STOCKCODE = PURCHORD_LINES.STOCKCODE
JOIN X_FREE_STOCK_VW ON X_FREE_STOCK_VW.STOCKCODE = PURCHORD_LINES.STOCKCODE
JOIN PURCHORD_HDR ON PURCHORD_HDR.SEQNO = PURCHORD_LINES.HDR_SEQNO
WHERE X_FREE_STOCK_VW.LOCATION = 1
AND STOCK_ITEMS.STOCK_CLASSIFICATION IN (0,10,20,200,210,220)
AND STOCK_ITEMS.STOCKCODE NOT LIKE '%2' AND STOCK_ITEMS.STOCKCODE NOT LIKE '%3'
AND (X_FREE_STOCK_VW.PhysicalQty - X_FREE_STOCK_VW.CommittedQty) <= 0
AND X_FREE_STOCK_VW.CommittedQty > 0
AND LEN(STOCK_ITEMS.STOCKCODE) > 6
AND STOCK_ITEMS.DESCRIPTION IS NOT NULL
AND STOCK_ITEMS.DESCRIPTION != ''
AND STOCK_ITEMS.SUPPLIERNO IN (1009,1024,1068,1115,1146,1170,1259,1306,1410,2768)
AND STOCK_ITEMS.X_BUYER = 'P'
AND ((PURCHORD_LINES.ORD_QUANT - PURCHORD_LINES.SUP_QUANT) > 0 AND (PURCHORD_LINES.SUP_QUANT/PURCHORD_LINES.ORD_QUANT)<0.9)
AND ((PURCHORD_HDR.STATUS<>2)
AND (PURCHORD_HDR.X_DELIVERYSTATUS<>4)
AND (PURCHORD_LINES.ORD_QUANT>0))
GROUP BY PURCHORD_LINES.STOCKCODE
, STOCK_ITEMS.DESCRIPTION
, X_FREE_STOCK_VW.PhysicalQty
, X_FREE_STOCK_VW.CommittedQty
, X_FREE_STOCK_VW.IncommingQty
, PURCHORD_LINES.HDR_SEQNO
, PURCHORD_LINES.ORD_QUANT
, PURCHORD_LINES.SUP_QUANT
, PURCHORD_HDR.X_PURCHASEORDERREF
)
,interim_data
as (
select *,row_number() over(partition by stockcode order by eta asc) as rnk
from data
)
select *
from iterim_data
where rnk=1
推荐阅读
- bash - 尝试创建 bash 脚本时出现 git 错误“致命:带有 -a 的路径没有意义。”
- marklogic - 何时使用集合与目录?
- sql - SQL 存储为 JSON 与向表中添加字段
- c++ - 关于向量中重复的 unique_ptr 项的所有权错误
- javascript - 按日期查找无功能
- python - 我可以在 python 3 中为描述符定义一个 add 方法吗?
- r - 将 RMatrix::Column 转换为 NumericVector
- sql - 如果要选择所有列,如何将 DISTINCT 子句和 MIN 函数应用于 WHERE 语句?
- python - 通过 Sendgrid API 发送电子邮件时出错
- c++ - C++ If Else 两个语句在不应该执行时执行