SQL Server中加入子查询的效率
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15095158/
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
Efficiency of joining subqueries in SQL Server
提问by user2112153
I have a customers and orders table in SQL Server 2008 R2. Both have indexes on the customer id (called id). I need to return details about all customers in the customers table and information from the orders table, such as details of the first order.
我在 SQL Server 2008 R2 中有一个客户和订单表。两者都有关于客户 ID（称为id）的索引。我需要返回客户表中所有客户的详细信息和订单表中的信息，例如第一个订单的详细信息。
I currently left join my customers table on a subquery of the orders table, with the subquery returning the information I need about the orders. For example:
我目前在订单表的子查询上加入我的客户表，子查询返回我需要的有关订单的信息。例如：
SELECT c.id
,c.country
,First_orders.product
,First_orders.order_id
FROM customers c
LEFT JOIN SELECT( id,
product
FROM (SELECT id
,product
,order_id
,ROW_NUMBER() OVER (PARTITION BY id ORDER BY Order_Date asc) as order_No
FROM orders) orders
WHERE Order_no = 1) First_Orders
ON c.id = First_orders.id
I'm quite new to SQL and want to understand if I'm doing this efficiently. I end up left joining quite a few subqueries like this onto the customers table in one select query and it can take tens of minutes to run.
我对 SQL 很陌生，想了解我是否有效地做到了这一点。我最终在一个选择查询中将很多这样的子查询加入到客户表中，并且可能需要数十分钟才能运行。
So am I doing this efficiently or can it be improved? For example, I'm not sure if my index on id in the orders table is of any use and maybe I could speed up the query by creating a temporary table of what is in the subquery first and creating a unique index on id in the temporary table so SQL Server knows idis now a unique column and then joining my customers table to this temporary table? I typically have one or two million rows in the customers and orders tables.
那么我这样做是有效的还是可以改进的？例如，我不确定我在 orders 表中的 id 索引是否有任何用处，也许我可以通过首先创建子查询中内容的临时表并在 id 中创建唯一索引来加快查询速度临时表所以 SQL Server 知道id现在是一个唯一的列，然后将我的客户表加入这个临时表？我通常在客户和订单表中有一两百万行。
Many thanks in advance!
提前谢谢了！
回答by sgeddes
You can remove one of your subqueries to make it a little more efficient:
您可以删除其中一个子查询以提高效率：
SELECT c.id
,c.country
,First_orders.product
,First_orders.order_id
FROM customers c
LEFT JOIN (SELECT id
,product
,order_id
,ROW_NUMBER() OVER (PARTITION BY id ORDER BY Order_Date asc) as order_No
FROM orders) First_Orders
ON c.id = First_orders.id AND First_Orders.order_No = 1
In your above query, you need to be careful where you place your parentheses as I don't think it will work. Also, you're returning product in your results, but not including in your nested subquery.
在上面的查询中，您需要小心放置括号的位置，因为我认为它不起作用。此外，您在结果中返回产品，但不包括在嵌套子查询中。
回答by Gordon Linoff
For someone who is just learning SQL, your query looks pretty good.
对于刚刚学习 SQL 的人来说，您的查询看起来不错。
The index on customers may or may not be used for the query -- you would need to look at the execution plan. An index on orders(id, order_date)could be used quite effectively for the row_numberfunction.
客户的索引可能会也可能不会用于查询——您需要查看执行计划。索引orders(id, order_date)可以非常有效地用于该row_number功能。
One comment is on the naming of fields. The field orders.idshould not be the customer id. That should be something like 'orders.Customer_Id`. Keeping the naming system consistent across tables will help you in the future.
一种评论是关于字段的命名。该字段orders.id不应是客户 ID。这应该类似于“orders.Customer_Id”。保持跨表的命名系统一致将在未来为您提供帮助。
回答by Utsav Anand
Try this...its easy to understand
试试这个……它很容易理解
;WITH cte
AS (
SELECT id
,product
,order_id
,ROW_NUMBER() OVER (
PARTITION BY id ORDER BY Order_Date ASC
) AS order_No
FROM orders
)
SELECT c.id
,c.country
,c1.Product
,c1.order_id
FROM customers c
INNER JOIN cte c1 ON c.id = c1.id
WHERE c1.order_No = 1
