sql server - subconsultas - ¿Cómo me uno a la primera fila de una subconsulta?

subquery sql server ejemplos

Question

Tengo una tabla de facturas y una tabla secundaria de datos relacionados relacionados por clave. En particular, para cada factura, solo me interesa la primera fila relacionada de la tabla secundaria. Dado que quiero una fila relacionada para cada clave de factura, ¿cómo puedo lograr esto?

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

Supongo que semánticamente, lo que estoy buscando es algo parecido al concepto de Top 1 c.CarrierName Group by InvoiceKey (o cuál sería el concepto de eso si fuera posible en T-SQL).

He pensado en hacer una combinación a la izquierda en una subconsulta, pero eso no parece muy eficiente. ¿Alguien tiene algún truco T-SQL para lograr esto de manera eficiente?

Edit : Lo siento, me olvidé de mencionar que esto es SQL Server 2000, así que aunque voy a dar votos para las respuestas actuales de SQL Server 2005/2008 que funcionarán, me temo que no puedo aceptarlas.

Answer 1

Así es como lo haría, usando una sintaxis ligeramente diferente a la suya (estilo MySQL), pero creo que también podría aplicarla a su solución:

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))

Esto tomará todos los registros de la factura y se unirá con uno (o cero) registros de los transportistas, específicamente el registro que tiene la misma factura y la primera.

Siempre que tenga un índice en Carriers.invoiceKey, el rendimiento de esta consulta debe ser aceptable.

Sebastian

Answer 2

En tales casos, a menudo empleo un dispositivo que aquí aplico a su ejemplo y describo a continuación:

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

Creo que esto es más o menos lo que Quassnoi ha publicado, solo que trato de evitar usar SELECT TOPs de esa manera.

Invoice se une a los Carriers función de su expresión de enlace ( InvoiceKey en este caso). Ahora, los Carriers pueden tener varias filas para el mismo InvoiceKey , por lo que necesitamos limitar la salida. Y eso se hace utilizando una tabla derivada.

La tabla derivada agrupa las filas de Carrier en función de la misma expresión que se usa para vincular las dos tablas ( InvoiceKey ).

Y hay otra forma: en lugar de unir la tabla derivada, podría usar IN (subquery) con el mismo efecto. Es decir, la consulta completa se vería así:

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)

Answer 3

Esto funciona para mí:

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

o

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

Answer 4

Siempre que los Carriers tengan una PRIMARY KEY llamada 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
)

Answer 5

group by carriername having max(invoicenumber)

para obtener el primer transportista para cada factura:

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

Answer 6

;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