查找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)
602392714
清零编程群