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 –