uma - statement sql server




SQL Server: Como ingressar na primeira linha (8)

, Outra abordagem usando expressão de tabela comum:

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

ou, no final talvez você gostaria de mostrar todas as linhas unidas?

versão separada por vírgulas aqui:

  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

Vou usar um exemplo concreto, mas hipotético.

Cada pedido normalmente tem apenas um item de linha :

Encomendas:

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

Mas, ocasionalmente, haverá um pedido com dois itens de linha:

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

Normalmente, ao mostrar os pedidos para o usuário:

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

Eu quero mostrar o item único no pedido. Mas com esse pedido ocasional contendo dois (ou mais) itens, os pedidos apareceriam 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

O que eu realmente quero é que o SQL Server escolha um , pois será bom o suficiente :

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

Se eu for aventureiro, posso mostrar ao usuário uma reticência para indicar que há mais de um:

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

Então a questão é como quer

  • eliminar linhas "duplicadas"
  • somente junte-se a uma das linhas, para evitar duplicação

Primeira tentativa

Minha primeira tentativa ingênua foi unir-se aos itens de linha " 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

Mas isso dá o erro:

A coluna ou prefixo 'Orders' não
coincidir com um nome de tabela ou alias
usado na consulta.

Presumivelmente porque a seleção interna não vê a mesa externa.


@Quassnoi resposta é boa, em alguns casos (especialmente se a tabela externa é grande), uma consulta mais eficiente pode ser com o uso de funções em janelas, como este:

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

Às vezes você só precisa testar qual consulta oferece melhor desempenho.


EDIT: nevermind, Quassnoi tem uma resposta melhor.

Para SQL2K, algo como isto:

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

Eu resolvo um problema semelhante usando LEFT JOIN e GROUP BY Orders.OrderNumber. Existe uma razão para não fazer isso dessa maneira?

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

Responderei sua pergunta de resposta com uma resposta em sua própria pergunta:

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

Juntando os dois juntos no OrderNumber dá:

OrderNumber  Quantity  Description
-----------  --------  -------------
22586        17        Trunion
22586        3         Girdle Spring

2 row(s) affected

Onde queríamos que retornasse apenas uma linha:

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

1 row(s) affected

É por isso que uso GROUP BY Orders.OrderNumber, que retorna apenas uma linha por OrderNumber.


Minha maneira favorita de executar essa consulta é com uma cláusula que não existe. Eu acredito que esta é a maneira mais eficiente de executar 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
    )

Mas eu não testei este método contra outros métodos sugeridos aqui.


Subconsultas correlacionadas são subconsultas que dependem da consulta externa. É como um loop for no SQL. A subconsulta será executada uma vez para cada linha na 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
)

Você poderia fazer:

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
  )

Isso requer um índice (ou chave primária) em LineItems.LineItemID e um índice em LineItems.OrderID ou será lento.


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

No SQL Server 2005 e acima, você poderia simplesmente substituir 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

Por favor, note que TOP 1 sem ORDER BY não é determinista: esta consulta você irá obter um item de linha por pedido, mas não está definido qual será.

Diversas invocações da consulta podem fornecer itens de linha diferentes para o mesmo pedido, mesmo que o subjacente não tenha sido alterado.

Se você quiser ordem determinística, você deve adicionar uma ORDER BY à consulta mais interna.





sql-server-2000