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

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

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

Источник

4 ответа

Решение

Уважать 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)
Источник

Попробуй это:

 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'
Источник

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

Источник

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

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

Источник
Другие вопросы по тегам