为什么我需要显式指定SQL中的所有列“GROUP BY”子句 - 为什么不是“GROUP BY *”？ [英] Why do I need to explicitly specify all columns in a SQL "GROUP BY" clause - why not "GROUP BY *"?
问题描述
这一直困扰着我 - 为什么SQL语句中的GROUP BY子句要求包含所有非聚合列？这些列应该默认包含 - 一种GROUP BY * - 因为我甚至无法运行查询，除非它们全部包含在内。每一列都必须是一个聚合或在GROUP BY中指定，但似乎任何未聚合的东西都应该自动分组。
也许它是ANSI-SQL标准，但即便如此，我也不明白为什么。有人可以帮我理解这个约定的必要吗？
很难确切地知道SQL语言的设计者在想什么当他们编写标准时， <但是这里是我的看法> 。
SQL通常要求您明确声明您的期望和您的意图。该语言不会尝试猜你的意思，并自动填入空格。 这是一件好事。
当您编写查询时，最重要的考虑因素是产生正确的结果。如果你犯了一个错误，那么SQL解析器通知你可能会更好，而不是猜测你的意图并返回可能不正确的结果。 SQL的声明性质（您声明您想要检索的内容而不是检索步骤）已经很容易导致无意中犯错误。 将fuzziniess引入语言语法不会使这一点更好。
事实上，我可以想到每种情况下语言允许的地方快捷键导致了问题。举例来说，自然连接 - 您可以忽略要加入的列的名称，并允许数据库根据列名来推断它们。一旦列名发生变化（因为它们随着时间的推移自然会发生变化） - 现有查询的语义会随着它们而改变。 这很糟糕......非常糟糕 - 您真的不希望这种 magic 发生在数据库代码的幕后。
然而，这种设计选择的一个结果是，SQL是一种冗长的语言，您必须明确地表达您的意图。这可能导致必须编写比您更多的代码可能会喜欢，并且抱怨为什么某些结构是如此冗长......但是在一天结束的时候 - 它就是这样。
This has always bothered me - why does the GROUP BY clause in a SQL statement require that I include all non-aggregate columns? These columns should be included by default - a kind of "GROUP BY *" - since I can't even run the query unless they're all included. Every column has to either be an aggregate or be specified in the "GROUP BY", but it seems like anything not aggregated should be automatically grouped.
Maybe it's part of the ANSI-SQL standard, but even so, I don't understand why. Can somebody help me understand the need for this convention?
It's hard to know exactly what the designers of the SQL language were thinking when they wrote the standard, but here's my opinion.
SQL, as a general rule, requires you to explicitly state your expectations and your intent. The language does not try to "guess what you meant", and automatically fill in the blanks. This is a good thing.
When you write a query the most important consideration is that it yields correct results. If you made a mistake, it's probably better that the SQL parser informs you, rather than making a guess about your intent and returning results that may not be correct. The declarative nature of SQL (where you state what you want to retrieve rather than the steps how to retrieve it) already makes it easy to inadvertently make mistakes. Introducing fuzziniess into the language syntax would not make this better.
In fact, every case I can think of where the language allows for shortcuts has caused problems. Take, for instance, natural joins - where you can omit the names of the columns you want to join on and allow the database to infer them based on column names. Once the column names change (as they naturally do over time) - the semantics of existing queries changes with them. This is bad ... very bad - you really don't want this kind of magic happening behind the scenes in your database code.
One consequence of this design choice, however, is that SQL is a verbose language in which you must explicitly express your intent. This can result in having to write more code than you may like, and gripe about why certain constructs are so verbose ... but at the end of the day - it is what it is.
这篇关于为什么我需要显式指定SQL中的所有列“GROUP BY”子句 - 为什么不是“GROUP BY *”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
