将最新的项目活动作为每项一行

Question

专家，我有一个查询，向我展示了订单和发票表的最新项目活动。我需要通过仅显示每个项目的结果来缩小更多范围 - 最新活动是订单还是发票...

SELECT A.ItemCode, A.ObjType, A.DocNum, A.LastActivity
FROM
(SELECT T0.[ItemCode], T1.[ObjType], T1.[DocNum], T1.[DocDate] AS 'LastActivity',
     ROW_NUMBER() OVER (PARTITION BY T0.ItemCode ORDER BY T0.DocEntry DESC) AS 'rownum'
 FROM INV1 T0
      INNER JOIN OINV T1 ON T0.[DocEntry]=T1.[DocEntry]
    WHERE T0.[ItemCode] = 'A00001_ test non inventory' OR T0.[ItemCode] = 'A00001'
 UNION ALL
 SELECT T0.[ItemCode], T1.[ObjType], T1.[DocNum], T1.[DocDate] AS 'LastActivity',
     ROW_NUMBER() OVER (PARTITION BY T0.ItemCode ORDER BY T0.DocEntry DESC) AS 'rownum'
 FROM RDR1 T0
    INNER JOIN ORDR T1 ON T0.[DocEntry]=T1.[DocEntry]
    WHERE T0.[ItemCode] = 'A00001_ test non inventory' OR T0.[ItemCode] = 'A00001'
    ) A
WHERE A.rownum=1;

Answer 1

将row_number移动到外部查询，并将整个内容包裹在另一个查询中：

SELECT A.ItemCode, A.ObjType, A.DocNum, A.LastActivity
FROM
(
    SELECT A.ItemCode, A.ObjType, A.DocNum, A.LastActivity,
            ROW_NUMBER() OVER (PARTITION BY ItemCode ORDER BY DocEntry DESC) AS 'rownum'
    FROM
    (
        SELECT T0.[ItemCode], T1.[ObjType], T1.[DocNum], T0.[DocEntry], T1.[DocDate] AS 'LastActivity'
        FROM INV1 T0
        INNER JOIN OINV T1 
            ON T0.[DocEntry]=T1.[DocEntry]
        WHERE T0.[ItemCode] = 'A00001_ test non inventory' 
        OR T0.[ItemCode] = 'A00001'
        UNION ALL
        SELECT T0.[ItemCode], T1.[ObjType], T1.[DocNum], T0.[DocEntry], T1.[DocDate] AS 'LastActivity'
        FROM RDR1 T0
        INNER JOIN ORDR T1 
            ON T0.[DocEntry]=T1.[DocEntry]
        WHERE T0.[ItemCode] = 'A00001_ test non inventory' 
        OR T0.[ItemCode] = 'A00001'
    ) A
) B
WHERE A.rownum=1;

提示：您可以通过用常见的表格替换派生表来使查询更易读：

WITH CTEUnion AS
(
        SELECT T0.[ItemCode], T1.[ObjType], T1.[DocNum], T0.[DocEntry], T1.[DocDate] AS 'LastActivity'
        FROM INV1 T0
        INNER JOIN OINV T1 
            ON T0.[DocEntry]=T1.[DocEntry]
        WHERE T0.[ItemCode] = 'A00001_ test non inventory' 
        OR T0.[ItemCode] = 'A00001'
        UNION ALL
        SELECT T0.[ItemCode], T1.[ObjType], T1.[DocNum], T0.[DocEntry], T1.[DocDate] AS 'LastActivity'
        FROM RDR1 T0
        INNER JOIN ORDR T1 
            ON T0.[DocEntry]=T1.[DocEntry]
        WHERE T0.[ItemCode] = 'A00001_ test non inventory' 
        OR T0.[ItemCode] = 'A00001'        
), CTERowNum AS
(
    SELECT ItemCode, ObjType, DocNum, LastActivity,
            ROW_NUMBER() OVER (PARTITION BY ItemCode ORDER BY DocEntry DESC) AS 'rownum'
    FROM CTEUnion
)
SELECT ItemCode, ObjType, DocNum, LastActivity
FROM CTERowNum
WHERE rownum=1;