Как присоединиться к первой строке подзапроса?

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

Select i.[Invoice Number],
       c.[Carrier Name]
From Invoice i
    Left Join Carriers c on i.[InvoiceKey] = c.[InvoiceKey]
Where -- what?

Я думаю, семантически говоря, что я ищу что-то похожее на концепцию Top 1 c.CarrierName Group by InvoiceKey (или что было бы понятием этого, если бы это было возможно в T-SQL.)

Я думал о том, чтобы сделать левое соединение в подзапросе, но это не кажется очень эффективным. У кого-нибудь есть трюки T-SQL для достижения этой эффективности?

Изменить. Извините, ребята, я забыл упомянуть, что это SQL Server 2000, поэтому, пока я собираюсь дать ответы на текущие ответы SQL Server 2005/2008, которые будут работать, Я принимаю их, я боюсь.

Ответ 1

При условии, что Carriers имеет PRIMARY KEY, называемый id:

SELECT  i.[Invoice Number],
        c.[Carrier Name]
FROM    Invoice i
JOIN    Carriers c
ON      c.id = 
        (
        SELECT  TOP 1 ID
        FROM    Carriers ci
        WHERE   ci.InvoiceKey = i.InvoiceKey
        ORDER BY
                id -- or whatever
        )

Ответ 2

Это работает для меня:

select ir.[Invoice Number], c.[Carrier Name]
from 
    (select ROW_NUMBER() over (order by i.[Invoice Number] asc) AS RowNumber, i.[Invoice Number], i.InvoiceKey
    from Invoice i) AS ir
left join Carriers c
on ir.InvoiceKey = c.InvoiceKey
where RowNumber = 1
union all
select ir.[Invoice Number], NULL as [Carrier Name]
from 
    (select ROW_NUMBER() over (order by i.[Invoice Number] asc) AS RowNumber, i.[Invoice Number]
    from Invoice i) AS ir
where RowNumber > 1

или

select TOP 1 i.[Invoice Number], c.[Carrier Name]
from Invoice i
left join Carriers c
on i.InvoiceKey = c.InvoiceKey
union all
select ir.[Invoice Number], NULL as [Carrier Name]
from 
    (select ROW_NUMBER() over (order by i.[Invoice Number] asc) AS RowNumber, i.[Invoice Number]
    from Invoice i) AS ir
where RowNumber > 1

Ответ 3

;with cteRowNumber as (
    select c.InvoiceKey, c.[Carrier Name], ROW_NUMBER() over (partition by c.InvoiceKey order by c.[Carrier Name]) as RowNum
        from Carriers c
)
select i.[Invoice Number],
       rn.[Carrier Name]
    from Invoice i
        left join cteRowNumber rn
            on i.InvoiceKey = rn.InvoiceKey
                and rn.RowNum = 1

Ответ 4

Вот как я это сделал бы, используя немного другой синтаксис, отличный от вашего (стиль MySQL), но я думаю, вы могли бы применить его и к своему решению:

SELECT i.invoiceNumber, c.carrierName
FROM Invoice as i
LEFT JOIN Carriers as c ON (c.id = (SELECT id FROM Carriers WHERE invoiceKey = i.invoiceKey ORDER BY id LIMIT 1))

Это займет все записи из счета-фактуры и соедините его с одной (или нулевой) записью от Carriers, в частности, с записью, которая имеет тот же счет-фактуру и только первый.

Пока у вас есть указатель на Carriers.invoiceKey, производительность этого запроса должна быть приемлемой.

Себастьян

Ответ 5

В таких случаях я часто использую устройство, которое я здесь применил к вашему примеру и опишу ниже:

SELECT
  i.[Invoice Number],
  c.[Carrier Name]
FROM Invoice i
  INNER JOIN Carriers c ON i.InvoiceKey = c.InvoiceKey
  INNER JOIN (
    SELECT MIN(ID) AS ID
    FROM Carriers
    GROUP BY InvoiceKey
  ) c_top ON c.ID = c_top.ID

Я думаю, это примерно то, что Quassnoi отправил, только я стараюсь избегать использования таких как SELECT TOP.

Invoice соединяется с Carriers на основе их связывающего выражения (InvoiceKey в этом случае). Теперь Carriers может иметь несколько строк для одного и того же InvoiceKey, поэтому нам нужно ограничить вывод. И это делается с использованием производной таблицы.

Производная таблица группирует строки из Carrier на основе того же выражения, которое используется для связывания двух таблиц (InvoiceKey).

И другой путь: вместо того, чтобы присоединяться к производной таблице, вы можете использовать IN (subquery) с тем же эффектом. То есть, полный запрос будет выглядеть следующим образом:

SELECT
  i.[Invoice Number],
  c.[Carrier Name]
FROM Invoice i
  INNER JOIN Carriers c ON i.InvoiceKey = c.InvoiceKey
    AND c.ID IN (SELECT MIN(ID) FROM Carriers GROUP BY InvoiceKey)

Ответ 6

group by carriername having max(invoicenumber)

чтобы получить первую несущую для каждого счета:

group by invoicenumber having max(carriername)
-- substitute the column you want to order by for carrier name to change which is 'first'