示例:查询采购订单某些条件下,物料字段重复时取采购日期最近的数据;
需求:过滤以下采购订单的数据,PK_MATERIAL重复时,取DBILLDATE最近的一条数据;
SELECT B.PK_MATERIAL,
B.NORIGTAXPRICE,
B.nqtorigprice,
H.PK_SUPPLIER,
H.DBILLDATE,
H.VBILLCODE,
H.CORIGCURRENCYID
FROM PO_ORDER H
LEFT JOIN PO_ORDER_B B ON H.PK_ORDER = B.PK_ORDER
WHERE H.VDEF10 = '0001A110000000001YFL'
AND H.PK_ORG = '0001A110000000001DFB'
AND B.PK_MATERIAL IN ('0001A11000000003XXXX', '0001A11000000003XXY0','0001A11000000003XXY1', '0001A11000000003XXSW','0001A11000000003Y2MV', '0001A11000000003XXY3')
AND NVL(H.DR, 0) = 0
AND NVL(B.DR, 0) = 0
实现:将以上SQL语句定义为表T
语法格式:row_number() over(partition by 分组列 order by 排序列 desc)
row_number() over()分组排序功能:
在使用 row_number() over()函数时候,over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。
FLAG为排序结果,可重新定义别名
SELECT *
FROM (SELECT T.*,
ROW_NUMBER() OVER(PARTITION BY T.PK_MATERIAL ORDER BY T.DBILLDATE DESC) FLAG
FROM (SELECT B.PK_MATERIAL,
B.NORIGTAXPRICE,
B.nqtorigprice,
H.PK_SUPPLIER,
H.DBILLDATE,
H.VBILLCODE,
H.CORIGCURRENCYID
FROM PO_ORDER H
LEFT JOIN PO_ORDER_B B ON H.PK_ORDER = B.PK_ORDER
WHERE H.VDEF10 = '0001A110000000001YFL'
AND H.PK_ORG = '0001A110000000001DFB'
AND B.PK_MATERIAL IN
('0001A11000000003XXXX', '0001A11000000003XXY0',
'0001A11000000003XXY1', '0001A11000000003XXSW',
'0001A11000000003Y2MV', '0001A11000000003XXY3')
AND NVL(H.DR, 0) = 0
AND NVL(B.DR, 0) = 0) T) TMP
此时,FLAG=1时,则为实际想要的数据;