transact sql script example




SQL Server: come partecipare alla prima riga (7)

, Un altro aproach usando l'espressione di tabella comune:

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, alla fine, forse ti piacerebbe mostrare tutte le righe unite?

versione separata da virgola qui:

  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

Userò un esempio concreto, ma ipotetico.

Normalmente, ogni ordine ha un solo elemento pubblicitario :

Ordini:

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

Ma occasionalmente ci sarà un ordine con due elementi pubblicitari:

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

Normalmente quando si mostrano gli ordini all'utente:

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

Voglio mostrare il singolo oggetto nell'ordine. Ma con questo ordine occasionale contenente due (o più) articoli, gli ordini sembrerebbero duplicati :

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

Quello che voglio veramente è che SQL Server ne scelga uno , poiché sarà abbastanza buono :

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

Se divento avventuroso, potrei mostrare all'utente, un'ellissi per indicare che ce n'è più di una:

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

Quindi la domanda è come entrambi

  • eliminare le righe "duplicate"
  • solo iscriversi a una delle righe, per evitare la duplicazione

Primo tentativo

Il mio primo tentativo ingenuo è stato quello di unirmi agli elementi pubblicitari " 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

Ma questo dà l'errore:

La colonna o il prefisso "Ordini" non corrisponde a un nome di tabella o alias utilizzato nella query.

Presumibilmente perché la selezione interna non vede il tavolo esterno.


EDIT: non importa, Quassnoi ha una risposta migliore.

Per SQL2K, qualcosa del genere:

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

Il mio modo preferito per eseguire questa query è con una clausola non esiste. Credo che questo sia il modo più efficiente per eseguire questo tipo di query:

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
    )

Ma non ho testato questo metodo rispetto ad altri metodi suggeriti qui.


La risposta @Quassnoi è buona, in alcuni casi (specialmente se la tabella esterna è grande), una query più efficiente potrebbe essere con l'uso di funzioni con finestra, come questa:

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 volte hai solo bisogno di testare quale query offre prestazioni migliori.


Potresti fare:

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
  )

Ciò richiede un indice (o chiave primaria) su LineItems.LineItemID e un indice su LineItems.OrderID o sarà lento.


Risolvo un problema simile utilizzando LEFT JOIN e GROUP BY Orders.OrderNumber. C'è una ragione per non farlo in questo modo?

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

Risponderò alla tua domanda di risposta con una risposta nella tua stessa domanda:

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

Unire i due insieme su OrderNumber fornisce:

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

2 row(s) affected

Dove volevamo che restituisse solo una riga:

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

1 row(s) affected

Questo è il motivo per cui utilizzo GROUP BY Orders.OrderNumber che restituisce solo una riga per OrderNumber.


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

In SQL Server 2005 e versioni successive, è possibile sostituire 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

Tieni presente che TOP 1 senza ORDER BY non è deterministico: questa query ti fornirà un elemento pubblicitario per ordine, ma non è definito quale sarà.

Più invocazioni della query possono fornire elementi pubblicitari diversi per lo stesso ordine, anche se il sottostante non è cambiato.

Se si desidera un ordine deterministico, è necessario aggiungere una clausola ORDER BY alla query più interna.





sql-server-2000