加入附加表列

我需要從另一個表中添加另一列，即 CusSorMaster+.SalesOrder.The 連接位於 dbo.SorMaster.SalesOrder = dbo.CusSorMaster+.SalesOrder 的位置。這不是我寫的，我需要對其進行操作以包含另一列。當我將表與列連接時，我得到的行與我想要的原始查詢不同。請協助並隨時提出任何問題。

SELECT RIGHT(dbo.SorMaster.SalesOrder,6) AS SalesOrder,
      dbo.SorMaster.CustomerPoNumber,
      dbo.SorDetail.SalesOrderLine,
      dbo.SorDetail.MStockCode,
      dbo.SorDetail.MStockDes,
      dbo.SorDetail.MOrderQty,
      dbo.SorDetail.MCusSupStkCode,
      dbo.SorDetail.LineType,
      dbo.SorMaster.OrderStatus,
      dbo.[CusSorDetailMerch+].DuparJobNumber,
      dbo.SorMaster.Customer,
      dbo.SorDetail.MShipQty,
      dbo.SorDetail.MBackOrderQty,
      CASE
          WHEN
      RIGHT(dbo.SorMaster.SalesOrder,6) = '211125'
          THEN dbo.SorDetail.MOrderQty
          ELSE dbo.SorDetail.MShipQty + dbo.SorDetail.MBackOrderQty
      END AS ShipPlusBackorderQty,
      dbo.InvAltStock.AltStockCode,
      RIGHT(STUFF(DATEPART(year,GETDATE()),1,0,'0'),2) + RIGHT(STUFF(DATEPART(week,GETDATE()),1,0,'0'),2) AS YearWeekNo,
      'B00ABD' + RIGHT(STUFF(DATEPART(year,GETDATE()),1,0,'0'),2) + RIGHT(STUFF(DATEPART(week,GETDATE()),1,0,'0'),2) + '' AS SerialPrefix,
      dbo.[CusSorDetailMerch+].InvoiceNumber
 FROM dbo.SorMaster WITH(NOLOCK)
INNER JOIN dbo.SorDetail WITH(NOLOCK) ON dbo.SorMaster.SalesOrder = dbo.SorDetail.SalesOrder 
LEFT OUTER JOIN dbo.InvAltStock ON dbo.SorDetail.MStockCode = dbo.InvAltStock.StockCode 
LEFT OUTER JOIN dbo.[CusSorDetailMerch+] ON dbo.SorDetail.SalesOrder = dbo.[CusSorDetailMerch+].SalesOrder
                                                     AND dbo.SorDetail.SalesOrderLine = dbo.[CusSorDetailMerch+].SalesOrderInitLine
WHERE(
      dbo.SorDetail.LineType = '1'
      OR
      dbo.SorDetail.LineType = '7')
     AND (
      dbo.SorMaster.OrderStatus <> '9')
     AND (
      dbo.SorMaster.OrderStatus <> '\')
     AND (
      dbo.SorMaster.OrderStatus <> '*')
     AND (
      dbo.[CusSorDetailMerch+].InvoiceNumber = ''
      OR
      dbo.[CusSorDetailMerch+].InvoiceNumber = '000000000000000'
      OR dbo.[CusSorDetailMerch+].InvoiceNumber IS NULL);

為了保留連接的“左”部分（= 您目前的行），您需要使用left outer join. 您可以在文件中了解外部連接。

順便說一句，如果您只需要您要加入的領域，那麼就不需要加入……

我冒昧：

• 重新格式化查詢，
• 重寫部分WHERE子句以使用IN關鍵字，
• 刪除關鍵字outer（在 T-SQL 中是可選的），
• 刪除WITH (nolock)選項，如果你真的需要它們，把它們放回去。

更新查詢：

SELECT  RIGHT(dbo.SorMaster.SalesOrder, 6) AS 'SalesOrder',
       dbo.SorMaster.CustomerPoNumber,
       dbo.SorDetail.SalesOrderLine,
       dbo.SorDetail.MStockCode,
       dbo.SorDetail.MStockDes,
       dbo.SorDetail.MOrderQty,
       dbo.SorDetail.MCusSupStkCode,
       dbo.SorDetail.LineType,
       dbo.SorMaster.OrderStatus,
       dbo.[CusSorDetailMerch+].DuparJobNumber,
       dbo.SorMaster.Customer,
       dbo.SorDetail.MShipQty,
       dbo.SorDetail.MBackOrderQty,
       CASE
           WHEN RIGHT(dbo.SorMaster.SalesOrder, 6)= '211125'
           THEN dbo.SorDetail.MOrderQty
           ELSE dbo.SorDetail.MShipQty + dbo.SorDetail.MBackOrderQty
       END AS 'ShipPlusBackorderQty',
       dbo.InvAltStock.AltStockCode,
       RIGHT(STUFF(DATEPART(year, GETDATE()), 1, 0, '0'), 2) + RIGHT(STUFF(DATEPART(week, GETDATE()), 1, 0, '0'), 2) AS 'YearWeekNo',
       'B00ABD' + RIGHT(STUFF(DATEPART(year, GETDATE()), 1, 0, '0'), 2) + RIGHT(STUFF(DATEPART(week, GETDATE()), 1, 0, '0'), 2) + '' AS 'SerialPrefix',
       dbo.[CusSorDetailMerch+].InvoiceNumber,
       dbo.SorMaster.SalesOrder,       -- new selected field (no join required)
       dbo.[CusSorMaster+].SalesOrder, -- new selected field
       dbo.[CusSorMaster+].*           -- new selected fields: all fields from [CusSorMaster+]
FROM dbo.SorMaster
JOIN dbo.SorDetail
   ON dbo.SorMaster.SalesOrder = dbo.SorDetail.SalesOrder
LEFT JOIN dbo.InvAltStock
   ON dbo.SorDetail.MStockCode = dbo.InvAltStock.StockCode
LEFT JOIN dbo.[CusSorDetailMerch+]
   ON  dbo.SorDetail.SalesOrder = dbo.[CusSorDetailMerch+].SalesOrder
   AND dbo.SorDetail.SalesOrderLine = dbo.[CusSorDetailMerch+].SalesOrderInitLine
LEFT JOIN dbo.[CusSorMaster+]                                               -- new outer join
   ON dbo.SorMaster.SalesOrder = dbo.[CusSorMaster+].SalesOrder
WHERE dbo.SorDetail.LineType in ('1', '7')                                  -- rewrote OR
 AND dbo.SorMaster.OrderStatus not in ('9', '\', '*')                      -- rewrote OR
 AND (dbo.[CusSorDetailMerch+].InvoiceNumber in ('', '000000000000000')    -- partially rewrote OR
   OR dbo.[CusSorDetailMerch+].InvoiceNumber IS NULL);

引用自：https://dba.stackexchange.com/questions/271940