将最大和案例添加到数据中？

Question

我试图在这里的代码中添加一些内容并更改此表，但是我在SQL中有点像Greenhorn，我正在努力地进行精神飞跃，以了解如何连接一些事情。

1. 我正在尝试添加Job的分区以获取第1,2,3,4 ...（因此Job 21980将有3行）

2. 我 想要显示每个作业的最高行。
3. 然后，我想添加作业的最大行no（因此， ：当行号不是最大行号时，我希望行中的数据为列：PO $$，WIP总计，每台PC，标准成本，差异，利润的百分比

当前的SQL生成了此数据：

SELECT  [Job #],
        ,[Date]
        ,[Variance Amt]
        ,[Job QTY] 
        ,[OpenQty] 
        ,[Part #] 
        ,[Material] 
        ,[PCS #] 
        ,[Matrl$$] 
        ,[Date Last Issue] 
        ,case when substring([PurchaseOrders],len([PurchaseOrders]),1) = '|' then substring([PurchaseOrders],1,len([PurchaseOrders])-1) else [PurchaseOrders] end [PurchaseOrders] 
        ,[PO$$] 
        ,[Date Last Rcvd] 
        ,[Wip Total] 
        ,[per pc] 
        ,[Standard Cost] 
        ,[DIFF] 
        ,[% of Profit] 
        FROM [VarianceView] 
        Order By [Job #]

我有点启动了步骤1和2，但这就是我所知道的。

ROW_NUMBER() OVER(PARTITION BY [Job #]
ORDER BY [Job #] DESC) AS 'RN'
,count(*) over(partition by [Job #]) as maxrn

一些示例数据

我感谢所有的帮助！

Answer 1

我认为您难题的缺失作品是a 常见的表表达式。除非它首先在公共表表达式或子查询中，否则您不能引用窗口函数。这就是我将解决您的问题的方式。您还需要在row_number（）函数中通过子句进行订单，以包括您想要的作业记录的方式。例如，您可能希望用最新的[日期上一期]作为最后一个记录或分区中的第一个记录对其进行评估。

如果您只想显示顶部作业＃记录，则需要添加另一个row_number（）函数（例如“ Rownumberdesc”），但随着[Job＃]下降。然后，您可以在外部查询中添加一个子句，以限制Rownumberdesc = 1的位置。

with cte as (
    select
        [Job #]
        ,[Date]
        ,[Variance Amt]
        ,[Job QTY] 
        ,[OpenQty] 
        ,[Part #] 
        ,[Material] 
        ,[PCS #] 
        ,[Matrl$] 
        ,[Date Last Issue] 
        ,case when substring([PurchaseOrders],len([PurchaseOrders]),1) = '|' then substring([PurchaseOrders],1,len([PurchaseOrders])-1) else [PurchaseOrders] end [PurchaseOrders] 
        ,[PO$] 
        ,[Date Last Rcvd] 
        ,[Wip Total] 
        ,[per pc] 
        ,[Standard Cost] 
        ,[DIFF] 
        ,[% of Profit] 
        ,ROW_NUMBER() OVER(PARTITION BY [Job #] ORDER BY [Date Last Issue]) AS 'RN'
        ,count(*) over(partition by [Job #]) as maxrn
      FROM [VarianceView] 
)

SELECT  [Job #]
        ,[Date]
        ,[Variance Amt]
        ,[Job QTY] 
        ,[OpenQty] 
        ,[Part #] 
        ,[Material] 
        ,[PCS #] 
        ,[Matrl$] 
        ,[Date Last Issue] 
        ,case when substring([PurchaseOrders],len([PurchaseOrders]),1) = '|' then substring([PurchaseOrders],1,len([PurchaseOrders])-1) else [PurchaseOrders] end [PurchaseOrders] 
        ,case when rn <> maxrn then 0 else [PO$] end as [PO$]
        ,[Date Last Rcvd] 
        ,case when rn <> maxrn then 0 else [Wip Total]      end as [Wip Total] 
        ,case when rn <> maxrn then 0 else [per pc]         end as [per pc] 
        ,case when rn <> maxrn then 0 else [Standard Cost]  end as [Standard Cost]
        ,case when rn <> maxrn then 0 else [DIFF]           end as [DIFF] 
        ,case when rn <> maxrn then 0 else [% of Profit]    end as [% of Profit] 
        ,rn as [ROW NO by JOB]
        ,maxrn as [MAX of ROW NO by JOB]
        FROM cte
        Order By [Job #]