为什么group by使sql查询速度如此之快
为什么group by使sql查询速度如此之快
提问于 2010-12-07 12:39:14
下面两个查询给出了相同的结果，但使用group by的查询运行得更快。这是否表明优化的查询会在可能的情况下倾向于使用group by？或者更有可能是我遇到了某种特殊情况？
更快的查询(对我来说不太直观)：
SELECT A.Advertiser
FROM (SELECT TOP (100) PERCENT Advertiser, [Final Status]
FROM dbo.Rehenas_View_2
GROUP BY [Final Status], Advertiser
HAVING ([Final Status] IS NULL)) AS A INNER JOIN
(SELECT TOP (100) PERCENT Advertiser, [Final Status]
FROM dbo.Rehenas_View_2 AS Rehenas_View_2_1
GROUP BY [Final Status], Advertiser
HAVING ([Final Status] = N'final')) AS B ON A.Advertiser = B.Advertiser
GROUP BY A.Advertiser较慢的查询(当我注意到速度差异时，我正试图简化上面的查询)
SELECT A.Advertiser
FROM (SELECT TOP (100) PERCENT Advertiser, [Final Status]
FROM dbo.Rehenas_View_2
WHERE ([Final Status] IS NULL)) AS A INNER JOIN
(SELECT TOP (100) PERCENT Advertiser, [Final Status]
FROM dbo.Rehenas_View_2 AS Rehenas_View_2_1
WHERE ([Final Status] = N'final')) AS B ON A.Advertiser = B.Advertiser
GROUP BY A.Advertiser回答 3
Stack Overflow用户
发布于 2010-12-07 17:43:22
根据您的评论，您希望广告商同时具有NULL和Final状态。
这应该会呈现所需的结果。DISTINCT通常意味着“我得到了副本……不知道为什么”，通常它隐藏了一个粉丝陷阱。在这种情况下，每个Advertiser可能有一个'final‘和空行的笛卡尔乘积。
SELECT DISTINCT A.Advertiser
FROM dbo.Rehenas_View_2 AS A,
dbo.Rehenas_View_2 AS B
WHERE A.[Final Status] IS NULL
AND B.[Final Status] = N'final'
AND A.Advertiser = B.Advertiser2个聚合的内连接：
SELECT Advertiser FROM
( SELECT Advertiser, COUNT(1) AS StatusCount
FROM dbo.Rehenas_View_2 WHERE [Final Status] IS NULL
GROUP BY Advertiser
HAVING StatusCount > 0) AS N,
(
SELECT Advertiser, COUNT(1) AS StatusCount
FROM dbo.Rehenas_View_2 WHERE [Final Status] = N'final'
GROUP BY Advertiser
HAVING StatusCount > 0) AS F
WHERE N.Advertiser = F.Advertiser另一个想法是使用CASE，并使用单个GROUP BY/HAVING计算NULL和FINALS
SELECT Advertiser FROM
( SELECT Advertiser,
SUM(CASE WHEN [Final Status] IS NULL THEN 1 ELSE 0 END) AS NullCount,
SUM(CASE WHEN [Final Status] = N'final' THEN 1 ELSE 0 END) AS FinalCount
FROM dbo.Rehenas_View_2 WHERE [Final Status] IS NULL
GROUP BY Advertiser
HAVING NullCount > 0 AND FinalCount > 0)我没有测试这种语法的环境。
Stack Overflow用户
发布于 2010-12-07 12:49:27
这两个查询似乎都要复杂得多。他们给出的结果不是和这个一样吗？
SELECT Advertiser
FROM dbo.Rehenas_View_2
WHERE [Final Status] IS NULL
UNION
SELECT Advertiser
FROM dbo.Rehenas_View_2
WHERE [Final Status] = N'final'这也应该要快得多。
当然，无论何时从视图中进行选择，都不能查看查询本身，还必须查看视图sql。除了是物化视图之外，它与使用子查询几乎相同。
Stack Overflow用户
发布于 2010-12-07 13:06:25
如果在这两个查询中删除最外层的group by，是否会看到返回的行数存在差异？这也许能解释这一切。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/4373449
复制相关文章
相似问题
腾讯云开发者
