合并查询

Question

我试图了解当其中一个查询返回多个记录时如何组合查询。

这是一份发票报告，我想在其中提取已开票产品的序列号。为了清楚起见，我将尽可能地缩写脚本。这是添加连续剧之前的脚本：

   SELECT ARM.fcustno AS [Cust No]
,      ARM.fbcompany AS [Cust Name]
,      ARM.fcinvoice AS [Invoice No]
,      ARM.fdgldate AS [Post Date]
,      ARI.fitem AS [Item No]
,      ARI.fprodcl AS [Prod Class]
,      ARI.fshipkey AS [Qty Invoiced]
,      ARI.fpartno AS [Part No]
,      ARI.frev AS [Part Rev]
,      ARI.FTOTPRICE AS [Net Invoiced]
,      ARM.fsono AS [Sales No]
,      SOM.fcusrchr2
FROM dbo.armast ARM
INNER JOIN dbo.aritem ARI ON ARM.FCINVOICE = ARI.FCINVOICE
INNER JOIN slcdpm SLC ON SLC.fcustno = ARM.fcustno
LEFT OUTER JOIN slcdpm_ext SLCE ON SLC.identity_column = SLCE.fkey_id
LEFT OUTER JOIN somast SOM ON SOM.fsono = ARM.fsono

这会返回已开票的行项目及其价格等。当我输入以下内容时：

SELECT ARM.fcustno AS [Cust No]
,      ARM.fbcompany AS [Cust Name]
,      ARM.fcinvoice AS [Invoice No]
,      ARM.fdgldate AS [Post Date]
,      ARI.fitem AS [Item No]
,      ARI.fprodcl AS [Prod Class]
,      ARI.fshipkey AS [Qty Invoiced]
,      ARI.fpartno AS [Part No]
,      ARI.frev AS [Part Rev]
,      ARI.FTOTPRICE AS [Net Invoiced]
,      ARM.fsono AS [Sales No]
,      SOM.fcusrchr2
,      LOTC.fcuseinlot 
FROM dbo.armast ARM
INNER JOIN dbo.aritem ARI ON ARM.FCINVOICE = ARI.FCINVOICE
INNER JOIN slcdpm SLC ON SLC.fcustno = ARM.fcustno
LEFT OUTER JOIN slcdpm_ext SLCE ON SLC.identity_column = SLCE.fkey_id
LEFT OUTER JOIN somast SOM ON SOM.fsono = ARM.fsono

--** New stuff below: ******
LEFT OUTER JOIN ShItem SHI ON SHI.fShipNo + SHI.fItemNo = ARI.fShipKey
LEFT OUTER JOIN ShSrce ON ShSrce.fcShipNo = SHI.fShipNo
                      AND ShSrce.fcItemNo = SHI.fItemNo
LEFT OUTER JOIN QaLotC LOTC ON LOTC.fcUseInDoc = ShSrce.fcShipNo + ShSrce.fcItemNo + ShSrce.fcSrcItmNo

问题是每个发票可以有多个 SHSRCE 记录。处理这个问题的最佳方法是什么？也许使用子查询来连接 LOTC.fcuseinlot 字段，以便为每条记录返回一个相应的值。

需要澄清的是，附加查询为每行项目发票返回 1 条以上的记录，因为可以在一个行项目上为多个序列号部分开具发票。理想情况下，我希望它们像 (NCC1701、R2D2、C3PO) 等一样连接起来。这就是为什么我考虑使用子查询来连接它们。

Answer 1

仍在等待 OP 澄清问题，但如果多个 SHSRCE 记录仍然只与一个序列号相关（我假设在 LOTC.fcuseinlot 中），

SELECT
    ARM.fcustno AS [Cust No] ,
    ARM.fbcompany AS [Cust Name] ,
    ARM.fcinvoice AS [Invoice No] ,
    ARM.fdgldate AS [Post Date] ,
    ARI.fitem AS [Item No] ,
    ARI.fprodcl AS [Prod Class] ,
    ARI.fshipkey AS [Qty Invoiced] ,
    ARI.fpartno AS [Part No] ,
    ARI.frev AS [Part Rev] ,
    ARI.FTOTPRICE AS [Net Invoiced] ,
    ARM.fsono AS [Sales No] ,
    SOM.fcusrchr2,
       MAX(LOTC.fcuseinlot)
FROM
    dbo.ARMAST ARM 
JOIN dbo.aritem ARI ON  ARI.FCINVOICE = ARM.FCINVOICE 
JOIN slcdpm SLC ON  SLC.fcustno = ARM.fcustno
LEFT JOIN slcdpm_ext SLCE ON  SLCE.fkey_id = SLC.identity_column
LEFT JOIN somast SOM ON  SOM.fsono = ARM.fsono
LEFT JOIN ShItem SHI ON  SHI.fShipNo + SHI.fItemNo = ARI.fShipKey
LEFT JOIN ShSrce ON  ShSrce.fcShipNo = SHI.fShipNo AND ShSrce.fcItemNo = SHI.fItemNo
LEFT JOIN QaLotC LOTC ON  LOTC.fcUseInDoc = ShSrce.fcShipNo + ShSrce.fcItemNo + ShSrce.fcSrcItmNo
GROUP BY
    ARM.fcustno ,
    ARM.fbcompany ,
    ARM.fcinvoice ,
    ARM.fdgldate ,
    ARI.fitem ,
    ARI.fprodcl ,
    ARI.fshipkey ,
    ARI.fpartno ,
    ARI.frev ,
    ARI.FTOTPRICE ,
    ARM.fsono ,
    SOM.fcusrchr2

---

您可以发布一些示例数据吗？

如果我没理解错的话，请看一下这种 sql，

create table tableA (id int, ref varchar(50))

insert into tableA
select 1, 3536757616
union select 1, 3536757617
union select 1, 3536757618
union select 2, 3536757628
union select 2, 3536757629
union select 2, 3536757630

连接引用

SELECT distinct
    id,
    stuff ( ( SELECT
                  '/ ' + ref 
              FROM
                  tableA tableA_1
              where tableA_1.id = tableA_2.id
    FOR XML PATH ( '' ) ) , 1 , 2 , '' )
from TableA tableA_2

我知道我可以简单地通过使用来

1   3536757616/ 3536757617/ 3536757618
2   3536757628/ 3536757629/ 3536757630

Answer 2

合并查询？就像UNION 一样吗？

Answer 3

我们真正需要知道的是，您将使用什么基础来选择哪些可能的记录具有您想要返回的值？这将为您确定最佳的可能性。

由于您没有添加任何列并且使用左联接（因此不会从联接中排除任何记录），除了延长处理时间之外，您希望通过添加这些联接来实现什么目的？

Answer 4

您的问题有一个 SQL Server 2000 标签，所以我不确定这对您是否可行。在 SQL Server 2005 及更高版本中，您可以创建自定义聚合函数。我使用聚合函数 FormDelimitedString ，它正是您所寻找的。

有一个页面这里描述了如何实现这个功能 - 它被写成作为 .NET 程序集，然后从查询中执行。有了这个，您的查询就变成了......

SELECT ARM.fcustno AS [Cust No]
,      ARM.fbcompany AS [Cust Name]
-- (a few fields skipped for brevity)
,      SOM.fcusrchr2
-- Aggregate function will combine multiple values into a single string - 'value1, value2, value3' etc
,   dbo.FormDelimitedString(LOTC.fcuseinlot) AS ConcatenatedValues
FROM dbo.armast ARM
INNER JOIN dbo.aritem ARI ON ARM.FCINVOICE = ARI.FCINVOICE
INNER JOIN slcdpm SLC ON SLC.fcustno = ARM.fcustno
LEFT OUTER JOIN slcdpm_ext SLCE ON SLC.identity_column = SLCE.fkey_id
LEFT OUTER JOIN somast SOM ON SOM.fsono = ARM.fsono
LEFT OUTER JOIN ShItem SHI ON SHI.fShipNo + SHI.fItemNo = ARI.fShipKey
LEFT OUTER JOIN ShSrce ON ShSrce.fcShipNo = SHI.fShipNo
                      AND ShSrce.fcItemNo = SHI.fItemNo
LEFT OUTER JOIN QaLotC LOTC ON LOTC.fcUseInDoc = ShSrce.fcShipNo + ShSrce.fcItemNo + ShSrce.fcSrcItmNo

-- Group by clause needed as its an aggregate function
GROUP BY ARM.fcustno, ARM.fbcompany, SOM.fcusrchr2