Pregunta SQL Server: Cómo unirse a la primera fila


Usaré un ejemplo concreto, pero hipotético.

Cada Orden normalmente tiene solo una Elemento en linea:

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 líneas de pedido:

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 esta orden ocasional que contiene dos (o más) elementos, los pedidos serían Aparecer ser duplicado:

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 tener SQL Server solo elija uno, como será 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 pongo aventurero, 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, ...

Entonces 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 al "TOP 1" artículos de línea:

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 prefijo 'Pedidos' no   coincidir con un nombre de tabla o alias   utilizado en la consulta.

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


578
2018-01-11 16:44


origen


Respuestas:


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 encima, podrías simplemente 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

924
2018-01-11 16:48



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

88
2018-04-06 21:25



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.


22
2018-01-11 16:50



La respuesta de @Quassnoi es buena, en algunos casos (especialmente si la tabla externa es grande), una consulta más eficiente podría ser con 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

Algunas veces solo necesito probar qué consulta proporciona un mejor rendimiento.


11
2018-03-03 16:14



Las subconsultas correlacionadas son sub consultas 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
)

6
2017-09-17 10:19



, Otro enfoque que usa 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, ¿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

5
2018-05-10 10:01



EDITAR: 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

4
2018-01-11 16:59



Resuelvo un problema similar usando 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 devolver solo una fila:

OrderNumber  Quantity  Description
-----------  --------  -------------
22586        17        Trunion

1 row(s) affected

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


2
2017-09-13 08:08



Probé la cruz, funciona bien, pero tarda un poco más. Columnas de línea ajustadas para tener un grupo máximo y agregado que mantuvo la velocidad y descartó 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

1
2018-02-14 21:47



Mi forma favorita de ejecutar esta consulta es con una cláusula no existente. 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 contra otros métodos sugeridos aquí.


1
2018-05-09 18:12