SQL Server中基于唯一标识符合并多行(SQL Server为GROUP_CONCAT)

我最初试图通过PHP来解决这个问题，但是我没有太多的运气…

如何根据一个键's值合并数组中的所有重复项?

因为我没有找到一个解决方案，我决定尝试通过我的SQL查询来解决我的问题。我需要知道的是如何"合并"在这个查询中返回的行之间的差异?

SELECT
  Item.ID,
  Item.ItemLookupCode,
  nitroasl_pamtable.ManufacturerPartNumber,
  SupplierList.ReorderNumber,
  Item.Notes,
  Item.Description,
  Item.ExtendedDescription,
  Item.Quantity,
  nitroasl_pamtable.SpoofStock,
  Item.Price,
  nitroasl_pamtable.PAM_Keywords
FROM 
   Item
JOIN 
   nitroasl_pamtable ON Item.ID = nitroasl_pamtable.ItemID
JOIN 
   SupplierList ON Item.ID = SupplierList.ItemID
WHERE 
   (Item.ItemLookupCode LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
   OR 
   (nitroasl_pamtable.ManufacturerPartNumber LIKE '%tp-ac1750%' 
    AND Price > 0.00 AND WebItem = 1)
   OR 
   (SupplierList.ReorderNumber LIKE '%tp-ac1750%' AND Price > 0.00 
    AND WebItem = 1)
   OR 
   (Item.Notes LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
   OR 
   (Item.Description LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
   OR 
   (Item.ExtendedDescription LIKE '%tp-ac1750%' AND Price > 0.00 
    AND WebItem = 1)
   OR 
   (nitroasl_pamtable.PAM_Keywords LIKE '%tp-ac1750%' AND Price > 0.00 
    AND WebItem = 1)
ORDER BY 
    Item.ItemLookupCode ASC;

我认为我需要(但还没有成功实现)

MySQL的GROUP_CONCAT等价项

我相信这个函数会做我需要的，但我使用的是SQL Server -不是MySQL。我似乎无法得到张贴的解决方案如何做到这一点为我工作…

我试过了:

最近，我尝试了MAX()和GROUP BY函数(一起)，但它选择了在重复行中返回的MAX值，从而返回单行，每列都有MAX值。

SELECT
    MAX(Item.ID) AS Id,
    Item.ItemLookupCode,
    MAX(nitroasl_pamtable.ManufacturerPartNumber) AS ManufacturerPartNumber,
    MAX(SupplierList.ReorderNumber) AS ReorderNumber,
    MAX( CAST(Item.Notes AS varchar(max)) ) AS Notes,
    MAX(Item.Description) AS Description,
    MAX( CAST(Item.ExtendedDescription AS varchar(max)) ) AS ExtendedDescription,
    MAX(Item.Quantity) AS Quantity,
    MAX(nitroasl_pamtable.SpoofStock) AS SpoofStock,
    MAX(Item.Price) AS Price,
    MAX(nitroasl_pamtable.PAM_Keywords) AS PAM_Keywords,
    MAX(Item.PictureName) AS PictureName
FROM 
    Item
LEFT JOIN 
    nitroasl_pamtable ON Item.ID = nitroasl_pamtable.ItemID
LEFT JOIN 
    SupplierList ON Item.ID = SupplierList.ItemID
WHERE 
    (Item.ItemLookupCode LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
    OR (nitroasl_pamtable.ManufacturerPartNumber LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
    OR (SupplierList.ReorderNumber LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
    OR (Item.Notes LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
    OR (Item.Description LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
    OR (Item.ExtendedDescription LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
    OR (nitroasl_pamtable.PAM_Keywords LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
GROUP BY 
    Item.ItemLookupCode
ORDER BY 
    Item.ItemLookupCode ASC

而不是丢弃每列的变体，我想把每列的所有返回值(用MAX丢弃)放入各自的/原始列中，用逗号分隔…

我需要什么:

数据库模式(示例)

在上面的文件中，您将看到上述SQL查询返回的四行。我想有一行返回，看起来像这样:

ID:

ItemLookupCode:

TP-AC1750

ManufacturerPartNumber:

Archer C7

ReorderNumber:

7681617, ARCHERC7, N82E16833704177

指出:

TP-LINK Archer C7 AC1750 Routr

描述:

TP-LINK Archer C7 AC1750 Routr

ExtendedDescription:

TP-Link Archer C7 Wireless-AC1750 Dual-Band Gigabit Router

数量:

0 (This should actually be a combined sum/total of the values in this column)

SpoofStock:

NULL (Same as Quantity - Should be sum / This value is different than Quantity)

价格:

129.95

PAM_Keywords:

NULL

我知道有更好的方法来写这个查询。我只是不是一个SQL家伙。这个查询/脚本是一个关键字搜索，它返回Microsoft Dynamics RMS数据库中的项目，并输出JSON，我用它来创建一个可以更改并重新提交到DB的产品列表。我使用SQL Server 2008 R2(如果有关系的话)。任何关于我如何使用我的查询的一些变化来完成上述输出的建议将非常感谢!由于

<标题>更新(SQLFiddle)

这里有一个SQLFiddle的链接，可以使用:)

SQLFiddle with No MAX Function

SQLFiddle with MAX Function(不是一个可行的解决方案，因为我丢失了数据)

这将让您开始，但仍然有一些不确定性围绕nitroasl_pamtable表，所以我没有包括它。

SELECT
  I.ID,
  I.ItemLookupCode,
  I.Notes,
  I.Description,
  I.ExtendedDescription,
  I.Quantity,
  I.Price,
  SL.ReorderNumbers,
  P.SpoofStock,
  P.ManufacturerPartNumber,
  P.PAM_Keywords
FROM
  Item I
  LEFT JOIN nitroasl_pamtable P
    ON I.ID = P.ItemID
  OUTER APPLY (
    SELECT
      ReorderNumbers = Substring((
        SELECT DISTINCT Convert(varchar(max), ', ' + SL.ReorderNumber)
        FROM SupplierList SL
        WHERE I.ID = SL.ItemID
        FOR XML PATH(''), TYPE
      ).value('.[1]', 'varchar(max)'), 3, 2147483647)
  ) SL
WHERE
  I.Price > 0.00
  AND I.WebItem = 1
  AND (
    I.ItemLookupCode LIKE '%tp-ac1750%'
    OR I.Notes LIKE '%tp-ac1750%'
    OR I.Description LIKE '%tp-ac1750%'
    OR I.ExtendedDescription LIKE '%tp-ac1750%'
    OR P.ManufacturerPartNumber LIKE '%tp-ac1750%'
    OR P.PAM_Keywords LIKE '%tp-ac1750%'
    OR EXISTS (
      SELECT *
      FROM dbo.SupplierList SL2
      WHERE
        I.ID = SL2.ItemID
        AND SL2.ReorderNumber LIKE '%tp-ac1750%'
    )
  )
ORDER BY
  I.ItemLookupCode ASC;

为了正确引入nitroasl_pamtable，对于您想要连接的每一列，您可以创建一个新的OUTER APPLY。您可以执行单个OUTER APPLY来一次获得需要正常聚合的所有列(例如Sum())。

然而，我想指出的是，这种连接会以一种可能导致错误评估或决策的方式模糊数据。从一个表中拉入3个值并将它们连接/求和将使它们看起来像一个单元，这可能是不正确的。

另一种连接可能有害的方式是使用重排序号。注意，我在这里放了一个DISTINCT，因为两个重复的再订货号——但它们来自不同的供应商。那么除了供应商之外，再订货号还有什么用呢?如果两个不同的产品在不同的供应商处有相同的再订货号，该怎么办?(例如，订单号BIGBOX在一个供应商处是一台电视机，但在另一个供应商处却是一个巨大的纸板箱。)

我不认为在查询中连接这些值是一个好主意。相反，UI应该分别显示查询(item作为一个行集，然后来自其他表的支持数据作为单独的行集)，然后以在UI中有意义的方式显示数据。

如果我理解正确的话，看起来您只需要连接ReorderNumber字段。您可以使用SQLCLR用户定义聚合(User-Defined Aggregate, UDA)来非常简单地完成此操作。

在SQLCLR函数，存储过程等(我是作者，但是这个聚合函数在免费版本中可用)的SQL#库中有一个预先完成的UDA，称为Agg_Join。使用它将使您的查询看起来如下:

SELECT
    MAX(Item.ID) AS Id,
    Item.ItemLookupCode,
    MAX(nitroasl_pamtable.ManufacturerPartNumber) AS ManufacturerPartNumber,
    SQL#.Agg_Join(SupplierList.ReorderNumber) AS ReorderNumber,
    MAX( CAST(Item.Notes AS varchar(max)) ) AS Notes,
    MAX(Item.Description) AS Description,
    MAX( CAST(Item.ExtendedDescription AS varchar(max)) ) AS ExtendedDescription,
    MAX(Item.Quantity) AS Quantity,
    MAX(nitroasl_pamtable.SpoofStock) AS SpoofStock,
    MAX(Item.Price) AS Price,
    MAX(nitroasl_pamtable.PAM_Keywords) AS PAM_Keywords,
    MAX(Item.PictureName) AS PictureName
FROM   Item
LEFT JOIN   nitroasl_pamtable
       ON Item.ID = nitroasl_pamtable.ItemID
LEFT JOIN  SupplierList
       ON Item.ID = SupplierList.ItemID
WHERE 
    (Item.ItemLookupCode LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
    OR (nitroasl_pamtable.ManufacturerPartNumber LIKE '%tp-ac1750%'
         AND Price > 0.00
         AND WebItem = 1)
    OR (SupplierList.ReorderNumber LIKE '%tp-ac1750%'
          AND Price > 0.00
          AND WebItem = 1)
    OR (Item.Notes LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
    OR (Item.Description LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
    OR (Item.ExtendedDescription LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
    OR (nitroasl_pamtable.PAM_Keywords LIKE '%tp-ac1750%' AND Price > 0.00 AND WebItem = 1)
GROUP BY      Item.ItemLookupCode
ORDER BY      Item.ItemLookupCode ASC;

只是提一下，SQL#的完整版本包括一个更功能的版本Agg_Join，称为Agg_JoinPlus，它允许排序，过滤重复，替换NULL s，更改分隔符等。

或者，如果您想自己创建它，在这种情况下，您可以自定义功能，我写了一篇文章，展示了创建用户定义聚合的示例，只需稍微更改一下即可进行字符串连接:充分利用SQL Server 2005 udt和UDAs(需要免费注册)。这是在SQL Server 2008出来之前写的，它有能力将MaxSize设置为-1，这样它就可以一次存储超过8000字节(这对于这种类型的操作来说比许多算术运算更有问题)。

另一个不需要订阅并且应该开箱即用的选项(我自己没有尝试过)是这个开源项目:

GROUP_CONCAT string aggregate for SQL Server

这个项目自2013-05-09以来一直没有更新，但我怀疑它会做你想要的，并且会像SQL#一样适合你的查询。Agg_Join和任何其他聚合。有一个安装脚本，GroupConcatInstallation。sql，在包含Assembly和T-SQL包装器对象的Installation Scripts文件夹中。

是的，所有这些聚合应该与PHP或其他任何东西一起工作，因为它们是查询的一部分，因此它是SQL Server进行处理，它与任何特定的客户端软件无关。

我认为我需要(但还没有成功实现)

我试过了:

我需要什么:

相关内容

最新更新

热门标签：