如何避免额外的内连接来优化这个查询？
如何避免额外的内连接来优化这个查询？
提问于 2013-03-25 16:04:31
有没有什么方法可以避免从customer表中获取客户名字和姓氏的内部连接，以及如何优化执行计划？
SELECT c1.firstname, c1.lastname, t.*
FROM customer c1
INNER JOIN
(select c.Customerid ,count(si.CustomerID) as 'No Of Orders'
from Customer c
LEFT OUTER join SalesInvoice si ON si.CustomerID = c.CustomerID
GROUP by c.CustomerID) as t
ON c1.CustomerID = t.Customerid
回答 3
Stack Overflow用户
回答已采纳
发布于 2013-03-25 16:23:38
有两种方法可以做到这点。一种方法是将所有非聚合列包括在group by列表中，如下所示：
SELECT
c.firstname,
c.lastname,
c.CustomerId,
count(si.CustomerID) as 'No Of Orders'
FROM
customer c
LEFT OUTER JOIN
SalesInvoice si ON si.CustomerID = c.CustomerID
GROUP BY
c.CustomerID,
c.firstname,
c.lastname另一种方法是在选择列表中使用聚合函数(min或max应该可以)：
SELECT
MIN(c.firstname) as firstname,
MIN(c.lastname) as lastname,
c.CustomerId,
count(si.CustomerID) as 'No Of Orders'
FROM
customer c
LEFT OUTER JOIN
SalesInvoice si ON si.CustomerID = c.CustomerID
GROUP BY
c.CustomerID我认为第一个查询可能在性能上更好。要提高整体性能，您需要创建一个包含索引或覆盖索引。
您可以通过以下链接了解所包含的索引：
- Why use the INCLUDE clause when creating an index?
- http://msdn.microsoft.com/en-IN/library/ms190806.aspx
Stack Overflow用户
发布于 2013-03-25 16:21:20
在OVER()子句中尝试此选项
SELECT c1.Customerid, c1.firstname, c1.lastname,
COUNT(si.CustomerID) OVER(PARTITION BY c1.CustomerID) AS 'No Of Orders'
FROM customer c1 LEFT JOIN SalesInvoice si ON si.CustomerID = c1.CustomerID为了提高性能，您需要此索引
CREATE INDEX ix_Customerid_Customer ON Customer(Customerid) INCLUDE(firstname, lastname)Stack Overflow用户
发布于 2013-03-25 16:11:37
试试那个：
SELECT
c.firstname,
c.lastname,
c.CustomerId,
count(si.CustomerID) as 'No Of Orders'
FROM
customer c
LEFT OUTER JOIN
SalesInvoice si ON si.CustomerID = c.CustomerID
GROUP BY
c.CustomerID,
c.firstname,
c.lastname页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/15610188
复制相关文章
相似问题
腾讯云开发者
