带有嵌套选择的 SQL Server ORDER BY/WHERE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2567167/
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
SQL Server ORDER BY/WHERE with nested select
提问by Echilon
I'm trying to get SQL Server to order by a column from a nested select. I know this isn't the best way of doing this but it needs to be done.
我试图让 SQL Server 按嵌套选择中的列进行排序。我知道这不是最好的方法，但需要这样做。
I have two tables, Bookings and BookingItems. BookingItems contains StartDate and EndDate fields, and there can be multiple BookingItems on a Booking. I need to find the earliest startdate and latest end date from BookingItems, then filter and sort by these values.
我有两个表，Bookings 和 BookingItems。BookingItems 包含 StartDate 和 EndDate 字段，并且一个 Booking 上可以有多个 BookingItems。我需要从 BookingItems 中找到最早的开始日期和最晚的结束日期，然后按这些值进行过滤和排序。
I've tried with a nested select, but when I try to use one of the selected columns in a WHERE or ORDER BY, I get an "Invalid Column Name".
我尝试使用嵌套选择，但是当我尝试在 WHERE 或 ORDER BY 中使用选定的列之一时，我得到一个“无效的列名”。
SELECT b.*, (SELECT COUNT(*) FROM bookingitems i WHERE b.BookingID = i.BookingID) AS TotalRooms,
(SELECT MIN(i.StartDate) FROM bookingitems i WHERE b.BookingID = i.BookingID) AS StartDate,
(SELECT MAX(i.EndDate) FROM bookingitems i WHERE b.BookingID = i.BookingID) AS EndDate
FROM bookings b LEFT JOIN customers c ON b.CustomerID = c.CustomerID WHERE StartDate >= '2010-01-01'
Am I missing something about SQL ordering? I'm using SQL Server 2008.
我是否缺少有关 SQL 排序的信息？我正在使用 SQL Server 2008。
回答by Chris Van Opstal
That shouldn't be a problem. Can you post the exact query that's failing?
那应该不是问题。你能发布失败的确切查询吗？
Also, grouping your results will be easier and likely faster then running the nested queries:
此外，与运行嵌套查询相比，对结果进行分组会更容易并且可能更快：
SELECT
TotalRooms = COUNT(*)
, StartDate = MIN(i.StartDate)
, EndDate = MAX(i.EndDate)
FROM bookings b
LEFT JOIN bookingitems bi
ON b.BookingID = bi.BookingID
GROUP BY b.BookingID
WHERE MIN(i.StartDate) >= '2010-01-01'
ORDER BY StartDate, EndDate
回答by Martin Smith
RE:
关于：
when I try to use one of the selected columns in a WHERE or ORDER BY, I get an "Invalid Column Name".
当我尝试在 WHERE 或 ORDER BY 中使用选定的列之一时，我收到“无效的列名”。
You will need to use different approaches for ORDER BY and WHERE. Can you post the exact code you were trying?
您将需要对 ORDER BY 和 WHERE 使用不同的方法。你能发布你正在尝试的确切代码吗？
The below is the (general) order of evaluation. Column Aliases are only available from step 5 onwards so are usable in ORDER BY but not WHERE
以下是评估的（一般）顺序。列别名仅从第 5 步开始可用，因此可用于 ORDER BY 但不能用于 WHERE
(5) SELECT (6) DISTINCT
(1) FROM
(2) WHERE
(3) GROUP BY
(4) HAVING
(7) ORDER BY
In the case of your posted Query you would need to do it something like this to avoid the error. I have spent zero time looking at the semantics to see if there is a better way though!
在您发布的查询的情况下，您需要这样做以避免错误。我花了零时间查看语义，看看是否有更好的方法！
SELECT b.*, (SELECT COUNT(*) FROM bookingitems i WHERE b.BookingID = i.BookingID) AS TotalRooms,
(SELECT MIN(i.StartDate) FROM bookingitems i WHERE b.BookingID = i.BookingID) AS StartDate,
(SELECT MAX(i.EndDate) FROM bookingitems i WHERE b.BookingID = i.BookingID) AS EndDate
FROM bookings b LEFT JOIN customers c ON b.CustomerID = c.CustomerID
WHERE (SELECT MIN(i.StartDate) FROM bookingitems i WHERE b.BookingID = i.BookingID) >= '2010-01-01'
This thread explains things in much more detail http://dbaspot.com/forums/sqlserver-programming/392124-when-can-we-use-column-alias-where-group-having-order.html#8
这个线程更详细地解释了事情http://dbaspot.com/forums/sqlserver-programming/392124-when-can-we-use-column-alias-where-group-have-order.html#8
