SQL и Coldfusion оставили таблицы объединений, получая повторяющиеся результаты в виде списка в одном столбце

говорит, что у меня есть две таблицы: Персоны (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. То, что мне действительно нужно получить, - это одна строка для каждого человека и все OrderNo, принадлежащие этому лицу в списке.

С coldfusion я могу запросить таблицу Person, перебрать каждую запись и для каждой записи сделать запрос в Orders и получить результаты для этого P_Id и поместить его в список и добавить его как новый, называемый "OrdersList", к моему первый запрос. Но у меня есть тысячи записей, которые означают тысячи запросов! Должен быть лучший способ сделать это!

Ответы

Ответ 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)

Ответ 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'

Ответ 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 состоит в том, чтобы избавиться от окончательного ', ', который будет добавлен в конце.

Здесь вы также можете увидеть другие примеры:

Ответ 4

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.

Нет, вы этого не сделаете, действительно, нет.

Вы можете сделать то, что вы уже сделали, и просмотреть результаты. Когда первый столбец меняет значение, вы знаете, что перешли к новому человеку. Одна из проблем может заключаться в том, что вы возвращаете имя снова и снова и снова, один раз для каждого идентификатора заказа. В этом случае возвратите два набора записей, каждый в том же порядке...

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, потому что вы можете сделать вывод о том, что человек не имеет ордеров при прохождении через два набора записей.)