Question SQL Server: Comment se joindre à la première rangée


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

Chaque Commande normalement a seulement un élément de campagne:

Ordres:

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 occasionnellement, il y aura une commande avec deux éléments de campagne:

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 montrer l'article unique sur la commande. Mais avec cette commande occasionnelle contenant deux (ou plusieurs) articles, les commandes 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 avoir SQL Server il suffit de choisir un, comme ce sera assez bien:

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

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

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

Donc, la question est de savoir comment

  • éliminer les lignes "dupliquées"
  • joindre uniquement à l'une des lignes, pour éviter la duplication

Premier essai

Ma première tentative naïve était de me joindre à la "TOP 1"éléments de campagne:

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 «Commandes» ne   correspondre avec un nom de table ou un nom d'alias   utilisé dans la requête.

Vraisemblablement parce que le select interne ne voit pas la table externe.


578
2018-01-11 16:44


origine


Réponses:


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

Dans SQL Server 2005 et ci-dessus, vous pouvez simplement remplacer INNER JOIN avec 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



Je sais que cette question a été répondue 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 fois, au lieu de 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

88
2018-04-06 21:25



Vous pourriez 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 ce sera lent.


22
2018-01-11 16:50



La réponse @Quassnoi est bonne, dans certains cas (surtout si la table externe est grande), une requête plus efficace pourrait être d'utiliser des fonctions fenêtrées, comme ceci:

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

Parfois vous juste besoin de tester quelle requête donne de meilleures performances.


11
2018-03-03 16:14



Les sous-requêtes corrélées sont des sous-requêtes qui dépendent de la requête externe. C'est comme une boucle for en SQL. La sous-requête sera exécutée une fois pour chaque ligne de la requête externe:

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



, Une autre approche utilisant l'expression de table commune:

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, à la fin peut-être que vous aimeriez montrer toutes les lignes jointes?

Version séparée par des virgules ici:

  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



EDIT: peu importe, 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

4
2018-01-11 16:59