Wie kann ich mehrere Spalten aus einer Unterabfrage (in SQL Server) auswählen, die für jeden Datensatz in der Hauptabfrage einen Datensatz (Top 1) haben soll?

Hier ist in der Regel, wie Sie zum auswählen mehrerer Spalten aus einer Unterabfrage:

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

Wenn das, was Sie letztlich versuchen, ist, die Werte aus der Zeile mit dem höchsten Wert für Foo (anstatt der max von Foo und der max von Foo2 - das ist NICHT das gleiche) dann sind die folgenden arbeiten in der Regel besser als eine Unterabfrage:

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

Du im Grunde sagst: gib mir die Zeile von B, wo ich nicht finden können, eine andere Zeile von B mit der gleichen SalesOrderID und eine größere Foo.

InformationsquelleAutor der Antwort Tom H

Müssen Sie eine Verknüpfung:

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'

unter der Annahme, dass b.id ist ein PRIMARY KEY auf B

In MS SQL 2005 und höher können Sie diese syntax verwenden:

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

Wird diese Option genau einen Datensatz aus B für jeden SalesOrderId.

InformationsquelleAutor der Antwort Quassnoi