为什么我需要在 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"中指定，但似乎任何未聚合的内容都应自动分组.
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.
也许它是 ANSI-SQL 标准的一部分，但即便如此，我也不明白为什么.有人能帮我理解这个约定的必要性吗?
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?
推荐答案
很难确切地知道 SQL 语言的设计者在编写标准时的想法，但这是我的意见.
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 要求您明确说明您的期望和意图.该语言不会尝试猜测你的意思"，而是自动填空.这是一件好事.
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.
当您编写查询时，最重要的考虑是它产生正确的结果.如果您犯了错误，最好让 SQL 解析器通知您，而不是而不是猜测您的意图并返回可能不正确的结果.SQL 的声明性质(在其中说明要检索的内容而不是检索方法的步骤)已经很容易在不经意间犯错误.在语言语法中引入模糊性不会使这更好.
事实上，我能想到的语言允许快捷方式的每种情况都会引起问题.以自然连接为例 - 您可以省略要连接的列的名称，并允许数据库根据列名称推断它们.一旦列名发生变化(随着时间的推移它们自然会发生变化)- 现有查询的语义随之变化.这很糟糕......非常糟糕 - 你真的不希望这种魔法发生在你的数据库代码的幕后.
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.
然而，这种设计选择的一个结果是，SQL 是一种冗长的语言，您必须在其中明确表达您的意图.这可能导致您不得不编写比您喜欢的更多的代码，并且抱怨为什么某些结构如此冗长......但归根结底 - 就是这样.
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屋！
