SQL和Coldfusion左连接表获取重复的结果作为一列中的列表 [英] SQL and Coldfusion left join tables getting duplicate results as a list in one column

问题描述

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

lets say I have two tables: Persons (P_Id, Name) and Orders (O_Id, OrderNo, P_Id)... I want to do a left join which would be:

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

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

This would give me multiple rows for the same Person with different OrderNo. What I really need to be able to get is one row for each person and all the OrderNo belonging to that person in a list.

通过coldfusion，我可以查询Persons表，循环每个记录，并为每个记录做一个查询订单，并获得该P_Id的结果，并将其放在一个列表，并将其添加为一个新的名为OrdersList到我的第一个查询。但我有成千上万的记录，这将意味着做成千上万的查询！

With coldfusion I can query the Persons table, loop over each record and for each record do a query on Orders and get the results for that P_Id and put it in a list and add it as a new called "OrdersList" to my first query. But I have thousands of records which would mean doing thousands of queries! There must be a better way to do this!

推荐答案

查找 FOR XML

Look up FOR XML - that will let you pivot the order numbers.

查看此信息

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

b $ b

输出是

The output is

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

(2 row(s) affected)

这篇关于SQL和Coldfusion左连接表获取重复的结果作为一列中的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！