SQL Server: Cómo unirse a la primera fila
Usaré un ejemplo concreto, pero hipotético.
Cada orden normalmente tiene solo una línea :
Órdenes:
OrderGUID OrderNumber
========= ============
{FFB2...} STL-7442-1
{3EC6...} MPT-9931-8A
LineItems:
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 cuando se muestran las órdenes al usuario:
SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM Orders
INNER JOIN LineItems
ON Orders.OrderID = LineItems.OrderID
Quiero mostrar el artículo individual en el pedido. Pero con este pedido ocasional que contiene dos (o más) artículos, los pedidos serían appear be duplicated :
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 elige 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 pongo aventurero, podría mostrar al usuario, una elipsis para indicar que hay más de una:
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 únase a una de las filas, para evitar duplicaciones
Primer intento
Mi el primer intento ingenuo fue unirse solo a los elementos de línea" 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 prefijo 'Orders' no coincidir con un nombre de tabla o alias utilizado en la consulta.
Presumiblemente porque la selección interna no ve la tabla externa.
10 answers
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 arriba, podrías simplemente reemplazar INNER JOIN
por 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 obtendrá un elemento de línea por pedido, pero no está definido cuál será.
Múltiples invocaciones de la consulta pueden darle diferentes elementos 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.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-09-21 11:20:38
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-04-06 22:34:38
Usted podría hacer:
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 primaria) en LineItems.LineItemID
y un índice en LineItems.OrderID
o será lento.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-01-11 16:50:24
@Quassnoi la respuesta 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
A veces solo necesita probar qué consulta da un mejor rendimiento.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-04-13 12:42:38
Las sub consultas correlacionadas son sub consultas que dependen de la consulta externa. Es como un bucle for en SQL. La sub-consulta 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
)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-09-17 10:38:08
, Otro enfoque usando 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 le 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-10 10:18:11
EDIT: nevermind, 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-01-11 17:23:56
Resuelvo un problema similar usando LEFT JOIN y GROUP BY Orders.Número de pedido. ¿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 |
+---------+----------+---------------+
Uniendo los dos juntos en OrderNumber da:
OrderNumber Quantity Description
----------- -------- -------------
22586 17 Trunion
22586 3 Girdle Spring
2 row(s) affected
Donde queríamos que devolviera solo una fila:
OrderNumber Quantity Description
----------- -------- -------------
22586 17 Trunion
1 row(s) affected
Esta es la razón por la que uso GRUPO POR Órdenes.OrderNumber que solo devuelve una fila por OrderNumber.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-09-14 07:38:46
Mi forma favorita de ejecutar esta consulta es con una cláusula not exists. 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í.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-09 18:12:59
Probado la cruz, funciona bien, pero toma un poco más de tiempo. Se ajustaron las columnas de línea para tener un grupo máximo y agregado que mantuvo la velocidad y eliminó 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-02-14 22:07:30