Comment joindre la première rangée

Question

Je vais utiliser un exemple concret, mais hypothétique.

Chaque Commandez n'a normalement qu'un seul poste d'exécution :

Les commandes :

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

Mais il arrive parfois qu'une commande comporte deux postes :

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

Normalement lors de l'affichage des commandes à l'utilisateur :

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

Je veux afficher l'article unique de la commande. Mais avec cette commande occasionnelle contenant deux (ou plus) articles, les commandes seraient apparaître être dupliqué :

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

Ce que je veux vraiment, c'est que le serveur SQL choisissez-en juste un comme il le sera suffisant :

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

Si je suis aventureux, je pourrais montrer à l'utilisateur une ellipse pour indiquer qu'il y en a plus d'une :

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

La question est donc de savoir comment

• éliminer les rangs "en double
• ne se joint qu'à une seule des lignes, pour éviter les doublons.

Première tentative

Ma première tentative naïve a été de me joindre uniquement à la " TOP 1 Les postes de la ligne " :

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

Mais cela donne l'erreur :

La colonne ou le préfixe "Ordres" n'a pas d'incidence sur les résultats.
correspondance avec un nom de table ou un nom d'alias
utilisé dans la requête.

Probablement parce que la sélection interne ne voit pas la table externe.

Answer 1

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 et supérieur, vous pouvez simplement remplacer 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

Veuillez noter que TOP 1 sans ORDER BY n'est pas déterministe : cette requête vous donnera un poste par commande, mais il n'est pas défini lequel ce sera.

Des invocations multiples de la requête peuvent vous donner des postes différents pour la même commande, même si le sous-jacent n'a pas changé.

Si vous voulez un ordre déterministe, vous devriez ajouter un ORDER BY à la requête la plus interne.

Exemple de sqlfiddle

Answer 2

Je sais que cette question a reçu une réponse il y a un certain temps, mais lorsqu'il s'agit de grands ensembles de données, les requêtes imbriquées peuvent être coûteuses. Voici une solution différente où la requête imbriquée ne sera exécutée qu'une seule fois, au lieu de l'être pour chaque ligne retournée.

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 3

Vous pourriez le faire :

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
  )

Cela nécessite un index (ou une clé primaire) sur LineItems.LineItemID et un index sur LineItems.OrderID ou il sera lent.

Answer 4

EDIT : nevermind, Quassnoi a une meilleure réponse.

Pour SQL2K, quelque chose comme ceci :

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 5

J'ai essayé la croix, ça marche bien, mais ça prend un peu plus de temps. J'ai ajusté les colonnes de ligne pour qu'elles aient un maximum et j'ai ajouté le groupe, ce qui a permis de conserver la vitesse et de supprimer l'enregistrement supplémentaire.

Voici la requête ajustée :

SELECT Orders.OrderNumber, max(LineItems.Quantity), max(LineItems.Description)
FROM Orders
    INNER JOIN LineItems 
    ON Orders.OrderID = LineItems.OrderID
Group by Orders.OrderNumber