SQL 和 Coldfusion 左连接表将重复结果作为一列中的列表

Question

假设我有两个表：Persons（P_Id，Name）和Orders（O_Id，OrderNo，P_Id）...我想做一个左连接：

SELECT Persons.Name, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.Name

这将为具有不同OrderNo的同一Person提供多行。我真正需要能够得到的是每个人一行以及列表中属于该人的所有 OrderNo。

通过 Coldfusion，我可以查询 Persons 表，循环遍历每条记录，并针对每条记录对 Orders 进行查询，获取该 P_Id 的结果，并将其放入列表中，并将其作为一个名为“OrdersList”的新添加到我的第一个查询中。但我有数千条记录，这意味着要进行数千次查询！一定有更好的方法来做到这一点！

Answer 1

查找FOR XML - 这将允许您旋转订单号。

检查一下

With Person AS
(
    Select 1 PersonId, 'John' PersonName
    Union Select 2, 'Jane'
),
Orders As
(
    Select 1 OrderId, 1 PersonId, Convert (DateTime, '1/1/2011') OrderDate
    Union Select 2, 1 , Convert (DateTime, '1/2/2011')
    Union Select 3, 1 , Convert (DateTime, '1/5/2011')
    Union Select 4, 1 , Convert (DateTime, '1/7/2011')
    Union Select 5, 1 , Convert (DateTime, '1/9/2011')
    Union Select 6, 2 , Convert (DateTime, '1/2/2011')
    Union Select 7, 2 , Convert (DateTime, '1/5/2011')
    Union Select 8, 2 , Convert (DateTime, '1/7/2011')
)
Select PersonId, 
(
    Select STUFF((SELECT  ', ' + cast(O.OrderId as nvarchar)
    FROM Orders O
    Where 1=1
        And O.PersonId = Person.PersonId
    FOR XML PATH('')), 1, 1, '') 
) OrderList
From Person

输出是

PersonId    OrderList
----------- -----------------------
1            1, 2, 3, 4, 5
2            6, 7, 8

(2 row(s) affected)

Answer 2

试试这个：

 declare @tmp table(PersonName varchar(100),OrderNo bigint)

  insert into @tmp(PersonName)
   select Person.Name from Persons
   where Person.Name = 'Edward'

    insert into @tmp(OrderNo)
    SELECT  Orders.OrderNo FROM Persons 
    JOIN Orders ON Persons.P_Id=Orders.P_Id 
    where  Persons.Name = 'Edward'

Answer 3

如果您使用的是 SQL Server，则可以使用 FOR XML PATH：

select 
    p.ID,
    p.Name 
    stuff((select ', ' + convert(varchar, o.OrderNo)
           from Orders o where o.P_Id = p.P_Id
           for xml path('')),
          1,2,'') [Orders]
from Persons p

STUFF 函数的作用是去掉最后的 ', '附在最后。

您还可以在此处查看另一个示例：

• 两个表之间的 SQL 相同单元需要 1 个单元格中的订单号
• SQL 查询在 SQL Server 中获取逗号分隔符以及按列分组的聚合结果

Answer 4

我真正需要的是能够为每个人获取一行以及列表中属于该人的所有 OrderNo。

不，你不需要，你真的真的不需要。

您可以执行已经完成的操作，并循环遍历结果。当第一列的值发生变化时，您就知道您已经换了一个新人。一个问题可能是您一次又一次地返回名称，每个订单 ID 一次。在这种情况下，返回两个记录集，每个记录集的顺序相同...

SELECT Persons.P_Id, Persons.Name
FROM Persons
ORDER BY Persons.Name

SELECT Persons.P_Id, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.Name

（您现在不需要 LEFT JOIN，因为您可以在循环遍历两个记录集时推断出没有顺序的人。）