SQL Server中的行复制

问题描述：

我有4个表如下。下表列出了四个表的概要。SQL Server中的行复制

InventoryTransferHeader

TrasferCode | TransferDate | FromOutlet| T0Outlet 
------------- |------------- |---------- |----------- 
SYS000022  | 2016-03-08 | 710  |  707

InventoryTransferLine

TransferCode |ItemCode |ItemDescription| Qty 
-------------|---------- |---------------|---- 
SYS000022 | 7000000064| ABCD   | 4  
SYS000022 | 5000000087| PQRS   | 4

InventoryTransferSerials

TransferCode |ItemCode |SerialNo 
-------------|---------- |------------- 
SYS000022 | 7000000064| 777777777798829 
SYS000022 | 7000000064| 777777777798830 
SYS000022 | 7000000064| 777777777798831 
SYS000022 | 7000000064| 777777777798832 
SYS000022 | 5000000087| 124536850888 
SYS000022 | 5000000087| 124536850889 
SYS000022 | 5000000087| 124536850890 
SYS000022 | 5000000087| 124536850891

个奥特莱斯

OutletCode | OutletDesc 
-----------|------------- 
707  | ABC 
710  | XYZ

我想下面我的输出。

TransferCode | TransferDate | FromOutlet | ToOutlet | ItemCode | ItemDescription |SerialNo | Qty 
-------------|--------------|------------|----------|----------|----------------|-------------|--------  
SYS000022 | 2016-03-08 | XYZ  | ABC  |5000000087|PQRS    |124536850888,124536850889,124536850890,124536850891|4  
SYS000022 | 2016-03-08 | XYZ  | ABC  |7000000064|ABCD    |777777777798829 ,777777777798830 ,777777777798831 ,777777777798832 |4

而不是获得两行我得到八行。这里是我使用的代码。

SELECT 
    ISNULL(InventoryTransferHeader.TransferCode,'') AS 'TransferId', 
    ISNULL(InventoryTransferHeader.TransferDate,'') AS 'TransferDate', 
    ISNULL(InventoryTransferLine.ItemCode,'') AS 'ItemCode', 
    ISNULL(InventoryTransferLine.ItemDescription,'') AS 'ItemDescription', 
    ISNULL(InventoryTransferLine.Qty,'') AS 'Quantity', 

    STUFF((SELECT '/' + InventoryTransferSerials.SerialNo 
      FROM InventoryTransferSerials 
      WHERE InventoryTransferSerials.ItemCode = InventoryTransferLine.ItemCode AND 
      InventoryTransferSerials.TransferCode = InventoryTransferLine.TransferCode 

      FOR XML PATH('')),1,1,'') AS 'SN', 


    ISNULL(InventoryTransferHeader.FromOutlet,'') AS 'outletFrom' , 
    ISNULL(F.OutletDesc,'') AS 'Outlets', 
    ISNULL(InventoryTransferHeader.TOOutlet,'') AS 'outletTO' , 
    ISNULL(T.OutletDesc,'') AS 'Outlets' 

FROM InventoryTransferHeader 
    INNER JOIN 
    Outlets F ON InventoryTransferHeader.FromOutlet = F.OutletCode 
    INNER JOIN 
    Outlets T ON InventoryTransferHeader.ToOutlet = T.OutletCode 
    LEFT OUTER JOIN 
    InventoryTransferLine ON InventoryTransferLine.TransferCode = InventoryTransferHeader.TransferCode 
    LEFT OUTER JOIN 
    InventoryTransferSerials ON InventoryTransferSerials.TransferCode = InventoryTransferLine.TransferCode 
    AND InventoryTransferSerials.ItemCode = InventoryTransferLine.ItemCode 


WHERE (InventoryTransferHeader.TransferCode = 'SYS000022')

答

删除

LEFT OUTER JOIN 
    InventoryTransferSerials ON InventoryTransferSerials.TransferCode = InventoryTransferLine.TransferCode 
    AND InventoryTransferSerials.ItemCode = InventoryTransferLine.ItemCode

你已经获得连续查询为SN

您加入InventoryTransferSerials所以你表InventoryTransferSerials的foreach InventoryTransferLine行中有4个序列号

这里我只使用了具有多个序列号的转码。但是，当我筛选单个序列号时，它会在序列号列中显示两次。只有当有多个序列号时，如何才能使用STUFF（）？ –

您能否举个例子，我不确定这个问题。如果您要筛选，您可以添加一个条件存在（SELECT InventoryTransferSerials.TransferCode FROM InventoryTransferSerials WHERE InventoryTransferSerials.ItemCode = InventoryTransferLine.ItemCode AND InventoryTransferSerials.TransferCode = InventoryTransferLine.TransferCode \t \t和InventoryTransferSerials.SerialNo = '124536850888'）和你需要添加子查询“SN” –

内部的过滤器或者你可以做一个内部联接这样内部连接（ SELECT DISTINCT ItemCode，TransferCode FROM InventoryTransferSerials \t \t其中InventoryTransferSerials.SerialNo =“124536850888” ）连续剧 on Serials.ItemCode = InventoryTransferLine.ItemCode AND Serials.TransferCode = InventoryTransferLine.TransferCode –

SQL Server中的行复制

相关推荐