您可以按如下方式使用分析函数(DENSE_RANK
)
:
SELECT
ITEM_NO,DATE AS LATEST_DEL_DATE,SUM(QTY) AS TOTAL_QTY
FROM
( SELECT DISTINCT
K.item_NO,K.QTY,K.DATE,DENSE_RANK() OVER(
PARTITION BY K.item_NO
ORDER BY K.DATE DESC NULLS LAST
) AS DR
FROM
DEL K
JOIN DELCONFIRM H ON (K.code_NO = H.code_NO)
JOIN ITEM O ON (K.item_NO = O.item_NO)
WHERE K.QTY <> 0
AND K.GOO_NO = '123'
)
WHERE DR = 1
GROUP BY ITEM_NO,DATE;
注意:如上述查询所示,使用标准ANSI连接。
干杯!
,
您可以JOIN
将交货表转换为感兴趣项目的最新交货日期的派生表,然后SUM
将该日期的交货数量:
SELECT m.item_code,m.max_date AS "Latest_delivery_date",SUM(d1.quantity) AS "Total_delivered"
FROM (SELECT item_code,MAX(date) AS max_date
FROM delivery
WHERE item_code = 123) m
JOIN delivery d1 ON d1.item_code = m.item_code AND d1.date = m.max_date
GROUP BY m.item_code,m.max_date
输出:
item_code Latest_delivery_date Total_delivered
123 2019-11-07 17
Demo on dbfiddle
,
使用聚合和窗口功能:
select id.*
from (select k.item_code,k.date,sum(k.qty) as sum(qty),row_number() over (partition by k.item_code order by k.date desc) as seqnum
from Del k join
Delconfirm h
on k.seq_no = h.seq_no join
ITEM o
on k.item_no = o.item_no
where k.QTY <> 0 and
k.item_no = '123'
group by k.item_no
) id
where seqnum = 1;
请特别注意使用正确,明确的标准 JOIN
语法。 请勿在{{1}}子句中使用逗号。
本文链接:https://www.f2er.com/3140269.html