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

OK, I have a system where orders get entered, and each order can have any number of bond, job and project numbers attached to it. The OrderDetails table contains an orderID used to go to the OrderNumbers table and get all attached "numbers" to this order, and then based on a numType column found there, it goes to one of the three tables (bond, job, project) and retrieves the actual number displayed and printed for the user.

好的,我有一个输入订单的系统,每个订单都可以附加任意数量的债券,工作和项目编号。 OrderDetails表包含一个orderID,用于转到OrderNumbers表并获取所有附加的“数字”到此订单,然后根据在那里找到的numType列,它转到三个表中的一个(bond,job,project)和检索为用户显示和打印的实际数字。

This means that trying to query an order to search for a certain project number can return 3 rows for the same order if that order had 3 project numbers attached to it.

这意味着,如果该订单附加了3个项目编号,则尝试查询订单以搜索某个项目编号可以为同一订单返回3行。

I am looking to be able to return my query results with only 1 row for the order, and a column which includes all 3 projects in delimited form, is this possible?

我希望能够返回我的查询结果,只有1行的订单,以及包含所有3个分隔形式的项目的列,这可能吗?

Here is my current query which returns multiple rows per order, when multiple numbers of the same type are attached to the order...

这是我当前的查询,当订单附加多个相同类型的数字时,每个订单返回多行...

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'

and here are the results I get....

这是我得到的结果....

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

and here is what I am trying to get...(basically flattening those first 4 columns)

这就是我想要的...(基本上扁平化前4列)

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

Any help is appreciated!!! Thanks, Doug

任何帮助表示赞赏!谢谢,道格

1 个解决方案

#1

By your example, it seems that there are no matching records on Bond and Job tables. If there were, you would get something like this:

根据您的示例,似乎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

That result set seems so "sparse" because you were joining OrderItemNumbers directly into your main query: this table has a distinct row for each child table (PONumbers, Job, Project), and so you get each join in a distinct, separated row. In order to avoid that, you could join OrderItemNumbers with each child table, and then join this subquery with OrderDetail (through the shared LoadOWID):

由于您将OrderItemNumbers直接加入到主查询中,因此该结果集似乎“稀疏”:此表为每个子表(PONumbers,Job,Project)都有一个不同的行,因此您将每个连接放在一个不同的分隔行中。为了避免这种情况,您可以将OrderItemNumbers与每个子表连接,然后将此子查询与OrderDetail(通过共享的LoadOWID)连接:

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'

That way you will get the following result:

这样你将得到以下结果:

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

I agree this is not exactly what you asked for, yet. You also seem to need some sort of "partial pivoting" over the Project table (at least).

我同意这不完全是你所要求的。您似乎还需要在Project表上进行某种“部分旋转”(至少)。

That also would be possible, in theory, but I wouldn't go that way. This is clearly a presentation requirement, and the cost to implement it on the SQL layer is simply too high.

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

Edit

In order to get a complete flat result set, you can do that (based on this SO answer):

为了获得完整的平面结果集,您可以这样做(基于此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

Which will result in this:

这将导致:

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

It works if you get many bonds x many PO's x many jobs for the same OWID too.

如果您获得许多债券x许多PO的x多个工作也适用于相同的OWID。

IMHO that illustrates quite well what I tried to say: yes, it does what you need, but it is ugly as hell. Of course, if you don't care, go ahead (just add some triggers you'll feel like it is 1995 again :P ).

恕我直言,很好地说明了我试图说的话:是的,它可以满足您的需求,但它很丑陋。当然,如果你不在乎,继续(只需添加一些触发器,你会觉得它再次是1995年:P)。

Hope it helps.

希望能帮助到你。

#1