sql - SQL - ROW_NUMBER 用于多条件 LEFT JOIN

问题描述

两个表存储每个产品的不同属性：CTI_ROUTING_VIEW 和 ORD_MACH_OPS

它们都按 SPEC_NO > MACH_SEQ_NO 组织，但每个表的序列号格式不同，因此不能用于 JOIN。ORCH_MACH_OPS 有 MACHINE 和 PASS_NO，这意味着如果一个产品通过同一台机器两次，具有较高 SEQ_NO 的行将是 PASS_NO 2、3 等。CTI_ROUTING_VIEW 不提供 PASS_NO，但我可以通过以下方式达到预期的结果：

SELECT TOP (1000) [SPEC_NO]
      ,[SPEC_PART_NO]
      ,[MACH_NO]
      ,[MACH_SEQ_NO]
      ,[BLANK_WID]
      ,[BLANK_LEN]
      ,[NO_OUT_WID]
      ,[NO_OUT_LEN]
      ,[SU_MINUTES]
      ,[RUN_SPEED]
      ,[NO_COLORS]
      ,[PRINTDIEID]
      ,[CUTDIEID]
      ,ROW_NUMBER() OVER (PARTITION BY MACH_NO ORDER BY MACH_SEQ_NO) as PASS_NO
  FROM [CREATIVE].[dbo].[CTI_ROUTING_VIEW]

我认为我可以使用这个人为的 PASS_NO 作为 JOIN 条件，但我似乎无法让它通过。这是我第一次使用 ROW_NUMBER() 所以我只是想知道我是否在 JOIN 语法中做错了什么。

SELECT rOrd.[SPEC_NO]
      ,rOrd.[MACH_SEQ_NO]
      ,rOrd.[WAS_REROUTED]
      ,rOrd.[NO_OUT]
      ,rOrd.[PART_COMP_FLG]
      ,rOrd.[SCHED_START]
      ,rOrd.[SCHED_STOP]
      ,rOrd.[MACH_REROUTE_FLG]
      ,rOrd.[MACH_DESCR]
      ,rOrd.REPLACED_MACH_NO
      ,rOrd.MACH_NO
      ,rOrd.PASS_NO
      ,rWip.MAX_TRX_DATETIME
      ,ISNULL(rWip.NET_FG_SUM*rOrd.NO_OUT,0) as NET_FG_SUM
      ,CASE
        WHEN rCti.BLANK_WID IS NULL then 'N//A'
        ELSE CONCAT(rCti.BLANK_WID, ' X ', rCti.BLANK_LEN)
       END AS SIZE
     ,ISNULL(rCti.PRINTDIEID,'N//A') as PRINTDIEID
     ,ISNULL(rCti.CUTDIEID, 'N//A') as CUTDIEID
     ,rStyle.DESCR as STYLE
     ,ISNULL(rCti.NO_COLORS, 0) as NO_COLORS
     ,CAST(CONCAT(rOrd.ORDER_NO,'-',rOrd.ORDER_PART_NO) as varchar) as ORD_MACH_KEY
  FROM [CREATIVE].[dbo].[ORD_MACH_OPS] as rOrd
    LEFT JOIN (SELECT DISTINCT
       [SPEC_NO]
      ,[SPEC_PART_NO]
      ,[MACH_NO]
      ,MACH_SEQ_NO
      ,[BLANK_WID]
      ,[BLANK_LEN]
      ,[NO_COLORS]
      ,[PRINTDIEID]
      ,[CUTDIEID]
      ,ROW_NUMBER() OVER (PARTITION BY MACH_NO ORDER BY MACH_SEQ_NO) as PASS_NO
    FROM [CREATIVE].[dbo].[CTI_ROUTING_VIEW]) as rCti
    ON rCti.SPEC_NO = rOrd.SPEC_NO
    and rCti.MACH_NO =
        CASE 
            WHEN rOrd.REPLACED_MACH_NO is null then rOrd.MACH_NO
            ELSE rOrd.REPLACED_MACH_NO
        END
    and rCti.PASS_NO  = rOrd.PASS_NO
    LEFT JOIN INVENTORY_ITEM_TAB as rTab
    ON rTab.SPEC_NO = rOrd.SPEC_NO
    LEFT JOIN STYLE_DESCRIPTION as rStyle
    ON rStyle.DESCR_CD = rTab.STYLE_CD
      LEFT JOIN (
      SELECT
        JOB_NUMBER
        ,FORM_NO
        ,TRX_ORIG_MACH_NO
        ,PASS_NO
        ,SUM(GROSS_FG_QTY-WASTE_QTY) as NET_FG_SUM
        ,MAX(TRX_DATETIME) as MAX_TRX_DATETIME
      FROM WIP_MACH_OPS
      WHERE GROSS_FG_QTY <> 0
      GROUP BY JOB_NUMBER, FORM_NO, TRX_ORIG_MACH_NO, PASS_NO) as rWip
    ON rWip.JOB_NUMBER = rOrd.ORDER_NO
    and rWip.FORM_NO = rOrd.ORDER_PART_NO
    and rWip.TRX_ORIG_MACH_NO = rOrd.MACH_NO
    and rWip.PASS_NO = rOrd.PASS_NO
  WHERE rOrd.SCHED_START > DATEADD(DAY, -20, GETDATE())

标签： sqlsql-server

sql - SQL - ROW_NUMBER 用于多条件 LEFT JOIN

问题描述

解决方案

推荐阅读