如何从子查询(在SQL Server中)中选择多个列，每个列在主查询中应该有一条记录(选择top 1) ?

I Know I can select a column from a subquery using this syntax:

我知道我可以使用这个语法从子查询中选择一个列:

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'

But what is the correct syntax to use multiple columns from a subquery (in my case a select top 1 subquery)? Thank you very much.

但是，使用子查询中的多个列(在我的例子中是select top 1子查询)的正确语法是什么?非常感谢。

5 个解决方案

#1

104

Here's generally how to select multiple columns from a subquery:

以下是如何从子查询中选择多个列的一般方法:

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

If what you're ultimately trying to do is get the values from the row with the highest value for Foo (rather than the max of Foo and the max of Foo2 - which is NOT the same thing) then the following will usually work better than a subquery:

如果您最终要做的是从Foo值最高的行中获取值(而不是Foo的最大值和Foo2的最大值——这不是一回事)，那么下面的操作通常比子查询更好:

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

You're basically saying, give me the row from B where I can't find any other row from B with the same SalesOrderID and a greater Foo.

你的意思是，给我从B到B的行，我找不到任何其他的行从B到相同的SalesOrderID和更大的Foo。

#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'

#3

You'll have to make a join:

你必须加入:

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'

, assuming that b.id is a PRIMARY KEY on B

,假设b。id是B上的主键

In MS SQL 2005 and higher you may use this syntax:

在MS SQL 2005或更高版本中，您可以使用以下语法:

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

This will select exactly one record from B for each SalesOrderId.

这将从B中为每个SalesOrderId选择一条记录。

#4

SELECT
    T1.PrimaryKey,
    T1.SomeColumn,
    MySubQuery.Col1,
    MySubQuery.Col2,
    MySubQuery.Col3
From
    Table1 T1
    LEFT JOIN (SELECT TOP 1 Col1, Col2, Col3, Fkey FROM Table 2 ORDER BY Col1 DESC) AS MySubQuery ON T1.PrimaryKey = MySubQuery.Fkey

#5

i think that is what you want.

我想这就是你想要的。

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'

#1

104