SQL Server：将一对多查询结果展开

Question

SQL Server：将一对多查询结果展开

sqlsql-serversql-server-2008one-to-manyflatten

6

好的，我有一个系统，订单会被输入进去，每个订单可以附带任意数量的债券、工作和项目编号。 OrderDetails表包含一个用于转到OrderNumbers表并获取此订单所有附加“编号”的orderID，然后基于那里找到的numType列，它转到三个表之一（bond, job, project），并检索出为用户显示和打印的实际编号。

这意味着尝试查询订单以搜索某个项目编号可能会对同一订单返回3行结果，如果该订单附带了3个项目编号。

我希望能够返回只有1行订单的查询结果，并且包括所有3个项目的列，用分隔符分隔，是否可能实现？

这是我的当前查询，当相同类型的多个编号附加到订单上时，会返回多个订单行...

SELECT 
    ISNULL(b.Descr,'') as Bond, ISNULL(PO.Description,'') as PO, 
    ISNULL(j.Descr,'') as Job, ISNULL(Proj.Descr,'') as Project, 
    d.OrdNbr, d.BillofLadingNbr, d.TripAndDeliveryTicketNbr
FROM 
    OrderDetail d 
LEFT JOIN 
    OrderItemNumbers n ON d.OWID = n.LoadOWID 
LEFT JOIN 
    Bond b ON n.NumberOWID = b.OWID AND n.NumType = 0
LEFT JOIN 
    PONumbers PO ON n.NumberOWID = PO.OWID AND n.NumType = 1 
LEFT JOIN 
    Job j ON n.NumberOWID = j.OWID AND n.NumType = 2 
LEFT JOIN 
    Project Proj ON n.NumberOWID = Proj.OWID AND n.NumType = 3 
WHERE 
    d.OWID = 'BTBD1004'

以下是我得到的结果...

Bond    PO              Job Project         OrdNbr      BillofLadingNbr
                            82001-8177-44   BTBD000063  BTBD000072  
        4.158 Kingsport                     BTBD000063  BTBD000072  
                            IME-81-1(118)   BTBD000063  BTBD000072

以下是我想要实现的内容...(基本上是将前四列展开)

Bond    PO              Job Project                         OrdNbr      BillofLadingNbr
        4.158 Kingsport     82001-8177-44, IME-81-1(118)    BTBD000063  BTBD000072

感谢您的帮助！谢谢，道格

- kingfenix

MySQL相对于SQL Server的一个优势是其拥有的珠宝级函数GROUP_CONCAT，据我所知。 - Mihai

1

这个答案可能会帮到你: https://dev59.com/L2sz5IYBdhLWcg3wR1vC - DLeh

不觉得这会对Dleh有所帮助，因为我最初需要根据单个值查询订单，比如where Project = 'IME-81-1(118)'，而我已经通过多个表进行了连接，与那个示例不同。这让我头疼，哈哈。 - kingfenix

使用stuff和for xml接近我所需的内容，但是我失去了指定项目编号和多值表连接的where子句的能力。真让人恼火。 - kingfenix

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- rsenna · Accepted Answer

从您的示例中看来，Bond和Job表中没有匹配的记录。如果有匹配的记录，您将会得到如下结果：

BOND        PO              JOB      PROJECT        ORDNBR     BILLOFLADINGNBR  
Some bond                                           BTBD000063 BTBD000072
            4.158 Kingsport                         BTBD000063 BTBD000072
                            Some job                BTBD000063 BTBD000072
                                     82001-8177-44  BTBD000063 BTBD000072
                                     IME-81-1(118)  BTBD000063 BTBD000072

您查询结果集似乎很“稀疏”，因为您直接将OrderItemNumbers表连接到主查询中：该表对应于每个子表（PONumbers、Job、Project）的不同行，因此您得到了每个连接的独立行。为了避免这种情况，您可以将OrderItemNumbers表与每个子表进行连接，然后通过共享的LoadOWID将此子查询与OrderDetail表连接：

SELECT
    ISNULL(b.Descr, '') as Bond,
    ISNULL(PO.Description, '') as PO, 
    ISNULL(j.Descr, '') as Job,
    ISNULL(Proj.Descr, '') as Project, 
    d.OrdNbr,
    d.BillofLadingNbr,
    d.TripAndDeliveryTicketNbr
FROM 
    OrderDetail d 
    LEFT JOIN (
        SELECT aux.*, n.*
        FROM Bond aux INNER JOIN OrderItemNumbers n ON n.NumberOWID = aux.OWID AND n.NumType = 0
    ) AS b
        ON b.LoadOWID = d.OWID
    LEFT JOIN (
        SELECT aux.*, n.*
        FROM PONumbers aux INNER JOIN OrderItemNumbers n ON n.NumberOWID = aux.OWID AND n.NumType = 1
    ) AS PO
        ON PO.LoadOWID = d.OWID
    LEFT JOIN (
        SELECT aux.*, n.*
        FROM Job aux INNER JOIN OrderItemNumbers n ON n.NumberOWID = aux.OWID AND n.NumType = 2
    ) AS j
        ON j.LoadOWID = d.OWID
    LEFT JOIN (
        SELECT aux.*, n.*
        FROM Project aux INNER JOIN OrderItemNumbers n ON n.NumberOWID = aux.OWID AND n.NumType = 3
    ) AS Proj
        ON Proj.LoadOWID = d.OWID
WHERE 
    d.OWID = 'BTBD1004'

那么你将获得以下结果：

Bond       PO               Job       Project         OrdNbr      BillofLadingNbr
Some bond  4.158 Kingsport  Some job  82001-8177-44   BTBD000063  BTBD000072  
Some bond  4.158 Kingsport  Some job  IME-81-1(118)   BTBD000063  BTBD000072

我同意，这并不完全符合您的要求。您似乎还需要在Project表上进行某种形式的“部分旋转”（至少如此）。

理论上也是可能的，但我不会这样做。这显然是一个演示需求，在SQL层实现它的成本太高了。

编辑

为了获得完整的扁平结果集，可以这样做（基于这个SO答案）：

DECLARE @bonds   VARCHAR(MAX)
DECLARE @numbers VARCHAR(MAX)
DECLARE @jobs    VARCHAR(MAX)
DECLARE @projs   VARCHAR(MAX)

DECLARE @owid VARCHAR(10) = 'BTBD1004'

SELECT
    @bonds = COALESCE(@bonds + ', ', '') + aux.Descr
FROM
    Bond aux
    INNER JOIN OrderItemNumbers n
        ON n.NumberOWID = aux.OWID AND n.NumType = 0
WHERE
    n.LoadOWID = @owid

SELECT
    @numbers = COALESCE(@numbers + ', ', '') + aux.Description
FROM
    PONumbers aux
    INNER JOIN OrderItemNumbers n
        ON n.NumberOWID = aux.OWID AND n.NumType = 1
WHERE
    n.LoadOWID = @owid

SELECT
    @jobs = COALESCE(@jobs + ', ', '') + aux.Descr
FROM
    Job aux
    INNER JOIN OrderItemNumbers n
        ON n.NumberOWID = aux.OWID AND n.NumType = 2
WHERE
    n.LoadOWID = @owid

SELECT
    @projs = COALESCE(@projs + ', ', '') + aux.Descr
FROM
    Project aux
    INNER JOIN OrderItemNumbers n
        ON n.NumberOWID = aux.OWID AND n.NumType = 3
WHERE
    n.LoadOWID = @owid

SELECT
    @bonds,
    @numbers, 
    @jobs,
    @projs,
    d.OrdNbr,
    d.BillofLadingNbr,
    d.TripAndDeliveryTicketNbr
FROM 
    OrderDetail d 
WHERE 
    d.OWID = @owid

这将导致以下结果：

Bond       PO               Job       Project                        OrdNbr      BillofLadingNbr
Some bond  4.158 Kingsport  Some job  82001-8177-44, IME-81-1(118)   BTBD000063  BTBD000072

如果您需要为同一个OWID获取多个债券、多个PO和多份工作，它就能正常工作。

在我看来，这很好地说明了我想说的：是的，它能满足您的需求，但它非常丑陋。当然，如果您不在意，那就继续使用吧（只要添加一些触发器，您会感觉自己回到了1995年 :P）。

希望有所帮助。