如何在SQL服务器中使用查询 [英] How this having query works in SQL server

问题描述

Hi Team，



请说明它的工作原理.....



SELECT 1，其中1 = 1，1 = 2 - 没有行返回

SELECT 1其中1 = 2，1 = 1 - 为什么在条件为假时返回1行



谢谢

Sukhen Dass



我尝试了什么：



i已经在google中尝试但没有找到满意的解释

Hi Team,

Please suggest the reason how it works.....

SELECT 1 where 1=1 having 1=2--No Row Returned
SELECT 1 where 1=2 having 1=1--Why 1 row returned while the condition is false

Thanks
Sukhen Dass

What I have tried:

i have tried in google but no satisfied explanations found

推荐答案

这很复杂，我同意它不应该发生 - 但是......它应该在实践中给出语法错误，而不是。这就是真正的问题。

WHERE应用于记录集，HAVING应用于组。并且你的SELECT不包含GROUP BY，因此没有任何组可供使用。

所以SQL正在做的是完全忽略WHERE子句，并处理整个输入记录集作为集团来经营。这是错的，但是你的查询是wronger！

如果你尝试添加GROUP BY，你会发现它的工作方式与你期望的完全一样：记录集由WHERE子句过滤，然后组是由HAVING过滤。

This is complex, and I agree that it shouldn't happen - but...it should give a syntax error in practice, and doesn't. And that's the real problem.
WHERE is applied to recordsets, HAVING is applied to groups. And your SELECT doesn't contain a GROUP BY, so there aren't any groups for it to work on.
So what SQL is doing is ignoring the WHERE clause completely, and treating the whole input recordset as the group to operate on. That's wrong, but your query is wronger!
If you try adding the GROUP BY, you'll find it works exactly as you expect: the record set is filtered by the WHERE clause, then the groups are filtered by the HAVING.

这篇关于如何在SQL服务器中使用查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！