HAVING和WHERE explenation [英] HAVING and WHERE explenation

问题描述

我已经在访问查询设计工具中构建了一个sql字符串，因为它已经非常努力尝试将其写下来了。


我已将此字符串转换为我的代码作为DoCmd.RunSql。我所做的是使用一个组合框来确保选择特定的记录。虽然在查询设计器中这样做，但我注意到它使用了HAVING子句来选择正确的标准。


我没有真正遇到它，因为我对查询很新但是对我来说肯定在HAVING和WHERE做同样的工作？


我应该在何时何地使用它们以及它们有什么限制。我希望能够进一步研究Allen Brownes表单过滤脚本，在那里他动态构建字符串进行过滤，以便可能使用它来创建我的sql字符串。


谢谢，


克里斯


Ps。我一直在谈论的字符串（使用查询工具时看起来很乱）

展开 | 选择 | Wrap < span class =codeDivider> | 行号

解决方案

< blockquote>它们相似，但不相同，不可互换。在执行GROUP BY之前使用WHERE子句。 HAVING子句用于GROUP BY之后产生的记录集。


因此，这有效：

展开 | 选择 | Wrap | 行号

几乎重复了Zepphead 80的说法


但要理解的重点是

在对结果进行分组之前将条件应用于每个记录的位置。

在应用分组后应用于记录集。

步骤1 根据where条件<选择记录br /> 第2步所选记录按组条件分组
步骤3 然后根据条件选择分组记录
步骤4 返回结果记录集

感谢您的回复。我创造了一些东西，一旦我理解了HAVING和WHERE，我现在就为此感到自豪。我现在有代码根据表单选择从主表中选择值，创建一个新表，然后从该表中获取值，并使用更多select语句在pareto分析图的记录源中使用。构建了一个where子句，允许使用Allen Brownes技术在第一个查询中过滤数据，创建动态字符串。

I have built a sql string within access query design tool as it has been quite an effort to try and write this off the bat.

I have transfered this string into my code as DoCmd.RunSql. What I have done is used a combo box to make sure specific records are selected. Whilst doing this in the query designer I noticed it used the HAVING clause in order to select the right criteria.

I havent really come across it much since I''m pretty new to queries but to me surely HAVING and WHERE do pretty much the same job?

When and where should I be using each and what limitations do they have. I will hopefully be looking further into Allen Brownes form filtering script where he dynamically builds the string for filtering to possibly use this to create my sql string.

Thanks,

Chris

Ps. The string I''ve been talking about (looks so messy when using the query tool)

Expand|Select|Wrap|Line Numbers

解决方案

They are similar, but not the same and not interchangeable. The WHERE clause is used prior to the GROUP BY being performed. The HAVING clause is used on the set of records that results after the GROUP BY.

So, this works:

Expand|Select|Wrap|Line Numbers

Pretty much a repeat of what Zepphead 80 said

but the essential point to understand is
the where conditions are applied to each record prior to the result being grouped.
Having is applied to the recordset after the grouping has been applied.

step 1 the records are selected according to the where conditions
step 2 the selected records are grouped according to the group conditions
step 3 the grouped records are then selected according to the having conditions
step 4 the resulting recordset is returned

Thank you both for the replies. I have created something I''m now quite proud of once I got my understanding of HAVING and WHERE. I now have code which selects value from my main table based on form selections, creates a new table which then has values taken from that table and used within the record source of a pareto analysis graph using more select statements. A where clause has been built to allow data to be filtered within the first query using Allen Brownes technique of creating a dynamic where string.

这篇关于HAVING和WHERE explenation的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！