如何从子查询（在 SQL Server 中）中选择多个列，主查询中的每条记录都应该有一个记录（选择前 1 个）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/583954/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I select multiple columns from a subquery (in SQL Server) that should have one record (select top 1) for each record in the main query?
提问by Tom H
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 子查询）？非常感谢。
回答by Tom H
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 的具有相同 SalesOrderID 和更大 Foo 的任何其他行。
回答by SANTOSH APPANA
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'
回答by Quassnoi
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.idis a PRIMARY KEYon B
，假定b.id是一个PRIMARY KEY上B
In MS SQL 2005and 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 Bfor each SalesOrderId.
这将为B每个SalesOrderId.
回答by gmetax
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'
回答by Alexander Lebedev
select t1.*, sq.*
from table1 t1,
(select a,b,c from table2 ...) sq
where ...
