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 表，遍历每条记录，并为每条记录查询Orders 并获取该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

输出是

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

(2 row(s) affected)

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