问题描述
假设我有两个表: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左连接表获取重复的结果作为一列中的列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!