variable - telegraf sql server




SQL Server: Comment se joindre à la première rangée (7)

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

Chaque commande ne comporte normalement qu'un seul é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 sembleraient être dupliquées :

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 SQL Server m'en choisisse un , car ce sera assez bon :

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 consistait à rejoindre uniquement les éléments de campagne " 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

Mais cela donne l'erreur:

La colonne ou le préfixe "Orders" ne correspond pas à 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.


, 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

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

Je résous un problème similaire en utilisant LEFT JOIN et GROUP BY Orders.OrderNumber. Y a-t-il une raison de ne pas le faire de cette façon?

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

Je répondrai à votre question avec une réponse à votre question:

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

Rejoindre les deux ensemble sur OrderNumber donne:

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

2 row(s) affected

Où nous le voulions pour retourner une seule ligne:

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

1 row(s) affected

C'est pourquoi j'utilise GROUP BY Orders.OrderNumber qui ne renvoie qu'une seule ligne par numéro de commande.


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

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
)

Ma façon préférée d'exécuter cette requête est avec une clause not exists. Je crois que c'est le moyen le plus efficace d'exécuter ce genre de requête:

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
    )

Mais je n'ai pas testé cette méthode par rapport à d'autres méthodes suggérées ici.


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 INNER JOIN , vous pouvez simplement remplacer INNER JOIN par 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




sql-server-2000