sql-server - SQL 使用 With 子句连接查询

Question

我需要加入 2 个查询，但我不知道如何加入，因为我添加了一个with子句，所以查询使它更有意义。

这是主要查询

    SELECT
    Id,
    Referencia,     
    UAP, 
    ConsumoWeek01 AS ConsumoWeek01, 
    ConsumoWeek02, 
    CASE 
        WHEN Stock IS NULL THEN 0
    ELSE    
        Stock
    END AS Stock ,
    PecasPorCaixa
FROM OPENQUERY(MACPAC, 
    'SELECT 
        ROW_NUMBER() OVER(ORDER BY A.RH6001 ASC) AS Id,
        A.RH6001 as Referencia, 
        A.RH6002 as UAP, 
        A.RH6030 as ConsumoWeek01, 
        A.RH6031 as ConsumoWeek02,
        IC130M.LLBLT1 as Stock,
        0 AS PecasPorCaixa
    FROM AUTO.D805DATPOR.TRP060H AS A
    LEFT JOIN AUTO.D805DATPOR.IC130M IC130M 
    ON A.RH6001 = IC130M.LLPPN AND
         IC130M.LLSTLC =
             CASE A.RH6002
                  WHEN ''UAP1'' THEN ''M1''
                  WHEN ''UAP2'' THEN ''M2''
                  WHEN ''UAP3'' THEN ''M3''
                  WHEN ''UAP4'' THEN ''M4''
                  WHEN ''UAP5'' THEN ''M5''
                  WHEN ''UAP6'' THEN ''M6''
                  WHEN ''UAPP'' THEN ''PROTOS''
                  WHEN ''EXT'' THEN ''EXTR''
                END     
    WHERE (A.RH6001 Not Like ''FS%'') 
    AND A.RH6030 <> 0
    ORDER BY Referencia DESC')

这是我想要加入的查询

SELECT * 
FROM OPENQUERY(MACPAC,
    'WITH maxFornecedor AS
     (
        SELECT
            YDA3REP.A3ARCD,
            YDA3REP.A3D5CD,
            ROW_NUMBER() OVER ( PARTITION BY YDA3REP.A3D5CD ORDER BY YDA3REP.A3A3DT DESC) AS Number
        FROM
            AUTO.YSACHAPOR.YDA3REP YDA3REP
     )              
        SELECT 
            YDAUREP.AUD5CD,
            YDAUREP.AUQCON
        FROM maxFornecedor F
        join AUTO.YSACHAPOR.YDAUREP YDAUREP
        ON F.A3ARCD = YDAUREP.AUARCD
        AND F.A3D5CD = YDAUREP.AUD5CD
        WHERE F.Number = 1 AND YDAUREP.AUD5CD LIKE ''M%'' 
        AND YDAUREP.AUD5CD NOT LIKE ''%P%'' 
        AND YDAUREP.AUA0NB > 1 
        AND YDAUREP.AUG6ST=''O''                
')

我尝试with在第二次选择的第二次查询中添加另一个子句，但它不起作用

标签： sql-server

Answer 1

要添加另一个 CTE 表，请使用以下语法：

with TableA 
AS (SELECT 1 AS [ColumnA])
, TableB 
AS (SELECT 2 AS [ColumnB])