代数关系 sql GROUP BY SORT BY ORDER BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28665635/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Algebra Relational sql GROUP BY SORT BY ORDER BY
提问by Cyberflow
I wanted to know what is the equivalent in GROUP BY, SORT BY and ORDER BY in algebra relational ?
我想知道代数关系中 GROUP BY、SORT BY 和 ORDER BY 中的等价物是什么？
回答by Simo Kivist?
Neither is possible in relational algebra but people have been creating some "extensions" for these operations (Note: in the original text, part of the text is written as subscript).
在关系代数中两者都不可能，但人们一直在为这些操作创建一些“扩展”（注意：在原文中，部分文本被写为下标）。
GROUP BY, According to the book Fundamentals of Database Systems (Elmasri, Navathe 2011 6th ed):
GROUP BY，根据《数据库系统基础》一书（Elmasri，Navathe 2011 第六版）：
Another type of request that cannot be expressed in the basic relational algebra is to specify mathematical aggregate functions on collections of values from the database.
...
We can define an AGGREGATE FUNCTION operation, using the symbol ? (pronounced script F)7, to specify these types of requests as follows:<grouping attributes> ? <function list> (R)
where <grouping attributes> is a list of attributes of the relation specified in R, and <function list> is a list of (<function> <attribute>) pairs. In each such pair, <function> is one of the allowed functions—such as SUM, AVERAGE, MAXIMUM, MINIMUM,COUNT—and <attribute> is an attribute of the relation specified by R. The resulting relation has the grouping attributes plus one attribute for each element in the function list.
另一种无法用基本关系代数表达的请求是在数据库中的值集合上指定数学聚合函数。
...
我们可以定义一个 AGGREGATE FUNCTION 操作，使用符号 ? （读作script F）7、指定这些类型的请求如下：<分组属性> ? <功能列表> (R)
其中 <grouping attributes> 是 R 中指定的关系的属性列表，<function list> 是 (<function> <attribute>) 对的列表。在每个这样的对中，<function> 是允许的函数之一——例如 SUM、AVERAGE、MAXIMUM、MINIMUM、COUNT——并且 <attribute> 是由 R 指定的关系的属性。结果关系具有分组属性加一个函数列表中每个元素的属性。
ORDER BY (SORT BY), another source:
Since a relation is a set (or a bag), there is no ordering defined for a relation. That is, two relations are the same if they contain the same tuples, irrespective of ordering. However, a user frequently wants the output of a query to be listed in some particular order. We can define an additional operator τ which sorts a relation if we are willing to allow an operator whose output is not a relation, but an ordered list of tuples.
For example, the expression
τLastName,FirstName(Student)
generates a list of all the Student tuples, ordered by LastName (as the primary sort key) then FirstName (as a secondary sort key). (The secondary sort key is used only if two tuples agree on the primary sort key. A sorting operation can list any number of sort keys, from most significant to least significant.)
由于关系是一个集合（或包），因此没有为关系定义排序。也就是说，如果两个关系包含相同的元组，则无论排序如何，它们都是相同的。然而，用户经常希望以某种特定顺序列出查询的输出。如果我们愿意允许输出不是关系而是元组有序列表的运算符，我们可以定义一个额外的运算符 τ 来对关系进行排序。
例如，表达式
τ姓，名（学生）
生成所有学生元组的列表，按姓氏（作为主排序键）然后名字（作为辅助排序键）排序。（仅当两个元组就主排序键达成一致时才使用辅助排序键。排序操作可以列出任意数量的排序键，从最重要到最不重要。）
回答by BJ Davey
You can use projection πfor the columns that you want group the table by them without aggregating (The PROJECT operation removes any duplicate tuples) as following:
您可以将投影π用于要按它们对表进行分组的列，而不进行聚合（PROJECT 操作会删除任何重复的元组），如下所示：
π c1,c2,c3 (R)
π c1,c2,c3 (R)
where c1,c2,c3 are columns(attributes) and R is the table(the relation)
其中 c1,c2,c3 是列（属性），R 是表（关系）
