Comment puis-je sélectionner plusieurs colonnes à partir d'une sous-requête (dans SQL Server) qui devrait avoir un enregistrement (select top 1) pour chaque enregistrement de la requête principale ?

Question

Je sais que je peux sélectionner une colonne dans une sous-requête en utilisant cette syntaxe :

SELECT A.SalesOrderID, A.OrderDate,
       (
       SELECT TOP 1 B.Foo
       FROM B
       WHERE A.SalesOrderID = B.SalesOrderID
       ) AS FooFromB
FROM A
WHERE A.Date BETWEEN '2000-1-4' AND '2010-1-4'

Mais quelle est la syntaxe correcte pour utiliser plusieurs colonnes d'une sous-requête (dans mon cas, une sous-requête select top 1) ? Merci beaucoup.

Answer 1

Voici comment sélectionner plusieurs colonnes à partir d'une sous-requête :

SELECT
     A.SalesOrderID,
     A.OrderDate,
     SQ.Max_Foo,
     SQ.Max_Foo2
FROM
     A
LEFT OUTER JOIN
     (
     SELECT
          B.SalesOrderID,
          MAX(B.Foo) AS Max_Foo,
          MAX(B.Foo2) AS Max_Foo2
     FROM
          B
     GROUP BY
          B.SalesOrderID
     ) AS SQ ON SQ.SalesOrderID = A.SalesOrderID

Si ce que vous essayez de faire est d'obtenir les valeurs de la ligne avec la valeur la plus élevée pour Foo (plutôt que le maximum de Foo et le maximum de Foo2 - ce qui n'est PAS la même chose), ce qui suit fonctionnera généralement mieux qu'une sous-requête :

SELECT
     A.SalesOrderID,
     A.OrderDate,
     B1.Foo,
     B1.Foo2
FROM
     A
LEFT OUTER JOIN B AS B1 ON
     B1.SalesOrderID = A.SalesOrderID
LEFT OUTER JOIN B AS B2 ON
     B2.SalesOrderID = A.SalesOrderID AND
     B2.Foo > B1.Foo
WHERE
     B2.SalesOrderID IS NULL

En gros, vous dites : donnez-moi la ligne de B où je ne peux trouver aucune autre ligne de B avec le même SalesOrderID et un Foo plus grand.

Answer 2

SELECT a.salesorderid, a.orderdate, s.orderdate, s.salesorderid
FROM A a
OUTER APPLY (SELECT top(1) *
             FROM B b WHERE a.salesorderid = b.salesorderid) as s
WHERE A.Date BETWEEN '2000-1-4' AND '2010-1-4'

Answer 3

Vous devrez faire un joint :

SELECT A.SalesOrderID, B.Foo
FROM A
JOIN B bo ON bo.id = (
     SELECT TOP 1 id
     FROM B bi
     WHERE bi.SalesOrderID = a.SalesOrderID
     ORDER BY bi.whatever
     )
WHERE A.Date BETWEEN '2000-1-4' AND '2010-1-4'

en supposant que b.id est un PRIMARY KEY en B

Sur MS SQL 2005 et plus, vous pouvez utiliser cette syntaxe :

SELECT SalesOrderID, Foo
FROM (
  SELECT A.SalesOrderId, B.Foo,
         ROW_NUMBER() OVER (PARTITION BY B.SalesOrderId ORDER BY B.whatever) AS rn
  FROM A
  JOIN B ON B.SalesOrderID = A.SalesOrderID
  WHERE A.Date BETWEEN '2000-1-4' AND '2010-1-4'
) i
WHERE rn

Ceci sélectionnera exactement un enregistrement dans B pour chaque SalesOrderId .

Answer 4

select t1.*, sq.*
from table1 t1,
   (select a,b,c from table2 ...) sq
where ...

Answer 5

Je pense que c'est ce que vous voulez.

SELECT 
      A.SalesOrderID, 
      A.OrderDate, 
      FooFromB.*

FROM A,
     (SELECT TOP 1 B.Foo
      FROM B
      WHERE A.SalesOrderID = B.SalesOrderID
      ) AS FooFromB

WHERE A.Date BETWEEN '2000-1-4' AND '2010-1-4'