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.