string_agg - SQL Server: Cómo unirse a la primera fila

select sql ejemplos

Question

Usaré un ejemplo concreto, pero hipotético.

Cada orden normalmente tiene solo una línea de pedido :

Pedidos:

OrderGUID OrderNumber
========= ============
{FFB2...} STL-7442-1
{3EC6...} MPT-9931-8A

Artículos de línea:

LineItemGUID Order ID Quantity Description
============ ======== ======== =================================
{098FBE3...} 1 7 prefabulated amulite
{1609B09...} 2 32 spurving bearing

Pero ocasionalmente habrá un pedido con dos artículos de línea:

LineItemID Order ID Quantity Description
========== ======== ======== =================================
{A58A1...} 6,784,329 5 pentametric fan
{0E9BC...} 6,784,329 5 differential girdlespring

Normalmente al mostrar los pedidos al usuario:

SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM Orders
INNER JOIN LineItems
ON Orders.OrderID = LineItems.OrderID

Quiero mostrar el único artículo en el pedido. Pero con este pedido ocasional que contiene dos (o más) artículos, los pedidos aparecerían duplicados :

OrderNumber Quantity Description
=========== ======== ====================
STL-7442-1 7 prefabulated amulite
MPT-9931-8A 32 spurving bearing
KSG-0619-81 5 panametric fan
KSG-0619-81 5 differential girdlespring

Lo que realmente quiero es que SQL Server solo elija uno , ya que será lo suficientemente bueno :

OrderNumber Quantity Description
=========== ======== ====================
STL-7442-1 7 prefabulated amulite
MPT-9931-8A 32 differential girdlespring
KSG-0619-81 5 panametric fan

Si me aventuro, podría mostrarle al usuario una elipsis para indicar que hay más de uno:

OrderNumber Quantity Description
=========== ======== ====================
STL-7442-1 7 prefabulated amulite
MPT-9931-8A 32 differential girdlespring
KSG-0619-81 5 panametric fan, ...

Así que la pregunta es cómo

• eliminar filas "duplicadas"
• Solo unirse a una de las filas, para evitar la duplicación.

Primer intento

Mi primer intento ingenuo fue unirme solo a las líneas de " TOP 1 ":

SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM Orders
INNER JOIN (
SELECT TOP 1 LineItems.Quantity, LineItems.Description
FROM LineItems
WHERE LineItems.OrderID = Orders.OrderID) LineItems2
ON 1=1

Pero eso da el error:

La columna o el prefijo ''Pedidos'' no coincide con un nombre de tabla o alias utilizado en la consulta.

Presumiblemente porque la selección interna no ve la tabla externa.

Answer 1

, Otro enfoque utilizando la expresión de tabla común:

with firstOnly as (
select Orders.OrderNumber, LineItems.Quantity, LineItems.Description, ROW_NUMBER() over (partiton by Orders.OrderID order by Orders.OrderID) lp
FROM Orders
join LineItems on Orders.OrderID = LineItems.OrderID
) select *
from firstOnly
where lp = 1

o, al final, tal vez te gustaría mostrar todas las filas unidas?

Versión separada por comas aquí:

 select *
from Orders o
cross apply (
select CAST((select l.Description + '',''
from LineItems l
where l.OrderID = s.OrderID
for xml path('''')) as nvarchar(max)) l
) lines

Answer 2

EDIT: no importa, Quassnoi tiene una mejor respuesta.

Para SQL2K, algo como esto:

SELECT
Orders.OrderNumber
, LineItems.Quantity
, LineItems.Description
FROM (
SELECT
Orders.OrderID
, Orders.OrderNumber
, FirstLineItemID = (
SELECT TOP 1 LineItemID
FROM LineItems
WHERE LineItems.OrderID = Orders.OrderID
ORDER BY LineItemID -- or whatever else
)
FROM Orders
) Orders
JOIN LineItems
ON LineItems.OrderID = Orders.OrderID
AND LineItems.LineItemID = Orders.FirstLineItemID

Answer 3

La respuesta de @Quassnoi es buena, en algunos casos (especialmente si la tabla exterior es grande), una consulta más eficiente podría ser el uso de funciones de ventana, como esta:

SELECT Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description
FROM Orders
LEFT JOIN
(
SELECT LineItems.Quantity, LineItems.Description, OrderId, ROW_NUMBER()
OVER (PARTITION BY OrderId ORDER BY (SELECT NULL)) AS RowNum
FROM LineItems
) LineItems2 ON LineItems2.OrderId = Orders.OrderID And RowNum = 1

A veces solo necesitas probar qué consulta da mejor rendimiento.

Answer 4

Las consultas secundarias correlacionadas son consultas secundarias que dependen de la consulta externa. Es como un bucle for en SQL. La subconsulta se ejecutará una vez para cada fila en la consulta externa:

select * from users join widgets on widgets.id = (
select id from widgets
where widgets.user_id = users.id
order by created_at desc
limit 1
)

Answer 5

Mi forma favorita de ejecutar esta consulta es con una cláusula no existe. Creo que esta es la forma más eficiente de ejecutar este tipo de consulta:

select o.OrderNumber,
li.Quantity,
li.Description
from Orders as o
inner join LineItems as li
on li.OrderID = o.OrderID
where not exists (
select 1
from LineItems as li_later
where li_later.OrderID = o.OrderID
and li_later.LineItemGUID > li.LineItemGUID
)

Pero no he probado este método con otros métodos sugeridos aquí.

Answer 6

Podrías hacerlo:

SELECT
Orders.OrderNumber,
LineItems.Quantity,
LineItems.Description
FROM
Orders INNER JOIN LineItems
ON Orders.OrderID = LineItems.OrderID
WHERE
LineItems.LineItemID = (
SELECT MIN(LineItemID)
FROM LineItems
WHERE OrderID = Orders.OrderID
)

Esto requiere un índice (o clave principal) en LineItems.LineItemID y un índice en LineItems.OrderID o será lento.

Answer 7

Probado la cruz, funciona bien, pero tarda un poco más. Las columnas de línea ajustadas para tener un grupo máximo y agregado que mantuvieron la velocidad y eliminaron el registro adicional.

Aquí está la consulta ajustada:

SELECT Orders.OrderNumber, max(LineItems.Quantity), max(LineItems.Description)
FROM Orders
INNER JOIN LineItems
ON Orders.OrderID = LineItems.OrderID
Group by Orders.OrderNumber

Answer 8

Resuelvo un problema similar utilizando LEFT JOIN y GROUP BY Orders.OrderNumber. ¿Hay alguna razón para no hacerlo de esta manera?

SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM Orders
LEFT JOIN LineItems
ON Orders.OrderID = LineItems.OrderID
GROUP BY Orders.OrderNumber

Responderé a tu pregunta de respuesta con una respuesta en tu propia pregunta:

Orders LineItems
+-------------+ +---------+----------+---------------+
| OrderNumber | | OrderID | Quantity | Description |
+-------------+ +---------+----------+---------------+
| 22586 | | 22586 | 17 | Trunion |
+-------------+ | 22586 | 3 | Girdle Spring |
+---------+----------+---------------+

Unir los dos en OrderNumber da:

OrderNumber Quantity Description
----------- -------- -------------
22586 17 Trunion
22586 3 Girdle Spring
2 row(s) affected

Donde queríamos que volviera solo una fila:

OrderNumber Quantity Description
----------- -------- -------------
22586 17 Trunion
1 row(s) affected

Es por eso que uso GROUP BY Orders.OrderNumber, que solo devuelve una fila por OrderNumber.

Answer 9

Sé que esta pregunta fue respondida hace un tiempo, pero cuando se trata de grandes conjuntos de datos, las consultas anidadas pueden ser costosas. Aquí hay una solución diferente donde la consulta anidada solo se ejecutará una vez, en lugar de para cada fila devuelta.

SELECT
Orders.OrderNumber,
LineItems.Quantity,
LineItems.Description
FROM
Orders
INNER JOIN (
SELECT
Orders.OrderNumber,
Max(LineItem.LineItemID) AS LineItemID
FROM
Orders INNER JOIN LineItems
ON Orders.OrderNumber = LineItems.OrderNumber
GROUP BY Orders.OrderNumber
) AS Items ON Orders.OrderNumber = Items.OrderNumber
INNER JOIN LineItems
ON Items.LineItemID = LineItems.LineItemID

Answer 10

SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM Orders
JOIN LineItems
ON LineItems.LineItemGUID =
(
SELECT TOP 1 LineItemGUID
FROM LineItems
WHERE OrderID = Orders.OrderID
)

En SQL Server 2005 y superior, simplemente puede reemplazar INNER JOIN con CROSS APPLY :

SELECT Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description
FROM Orders
CROSS APPLY
(
SELECT TOP 1 LineItems.Quantity, LineItems.Description
FROM LineItems
WHERE LineItems.OrderID = Orders.OrderID
) LineItems2

Tenga en cuenta que TOP 1 sin ORDER BY no es determinista: esta consulta le proporcionará una línea de pedido por orden, pero no se define cuál será.

Las múltiples invocaciones de la consulta pueden darle diferentes artículos de línea para el mismo orden, incluso si el subyacente no cambió.

Si desea un orden determinista, debe agregar una cláusula ORDER BY a la consulta más interna.