dbms标识符无效_DBMS中的聚合运算符（分组依据和具有子句）

dbms标识符无效

综合运营商 (Aggregate Operators)

To calculate aggregate values, one requires some aggregate operators to perform this task. These operators run over the columns of a relation. The total number of five aggregate operators is supported by SQL and these are:

为了计算合计值 ，需要一些合计运算符来执行此任务。 这些运算符在关系的列上运行。 SQL支持五个聚合运算符的总数，它们是：

1. COUNT – To calculate the number of values present in a particular column.

   COUNT –计算特定列中存在的值的数量。

2. SUM – To calculate the sum of all the values present in a particular column.

   SUM –计算特定列中所有值的总和。

3. AVG – To calculate the average of all the values present in a particular column.

   AVG –计算特定列中所有值的平均值。

4. MAX – To find the maximum of all the values present in a particular column.

   MAX –查找特定列中所有值的最大值。

5. MIN – To find the minimum of all the values present in a particular column.

   MIN –查找特定列中所有值的最小值。

Mostly, with COUNT, SUM, and AVG, DISTINCT is specified in conjunction in order to eliminate any duplicate data. But for MAX and MIN, DISTINCT is not required to be specified as that will anyway not going to affect the output.

通常，使用COUNT ， SUM和AVG时 ，将DISTINCT一起指定以消除任何重复数据。 但是对于MAX和MIN ，则不需要指定DISTINCT ，因为那样将不会影响输出。

For example: Find the average salary of all the employees.

例如：查找所有员工的平均工资。

SELECT AVG (E.salary)
FROM Employee E

按条款分组 (GROUP BY Clause)

GROUP BY Clause is used to group the attributes with similar features under the given condition.

GROUP BY子句用于在给定条件下对具有相似特征的属性进行分组。

Let us consider a table of student.

让我们考虑一张学生桌。

ID	Name	Marks	Section
1	Shiv	89	A
2	Parth	78	B
3	Ankush	95	A
4	Nimish	83	B

ID	名称	分数	部分
1个	希夫	89	一个
2	帕斯	78	乙
3	安库什	95	一个
4	尼米什人	83	乙

Question: Find the highest marks of the student for each section.

问题：找出每个部分学生的最高分数。

To find the highest marks section wise, we need to write two queries as:

为了明智地找到最高分，我们需要编写两个查询：

SELECT MAX (S.Marks)
FROM Student S
WHERE S.Section = 'A'

SELECT MAX (S.Marks)
FROM Student S
WHERE S.Section = 'B'

As such, if we have many sections, we have to write the query that number of time. This looks quite lengthy.

这样，如果我们有很多部分，我们必须以该次数编写查询。 这看起来很长。

GROUP BY clause made the solution easier as we don't require writing the queries the number of times of section, Instead, we write:

GROUP BY子句使解决方案更容易，因为我们不需要编写查询来查询段的次数，而是编写：

SELECT MAX (S.Marks)
FROM Student S
GROUP BY S.Section

In this way, writing just one query, we will get the expected output.

这样，只编写一个查询，我们将获得预期的输出。

ID	Name	Marks	Section
3	Ankush	95	A
4	Nimish	83	B

ID	名称	分数	部分
3	安库什	95	一个
4	尼米什人	83	乙

有条款 (HAVING Clause)

HAVING Clause determines whether the answer needs to be generated for a given condition or not.

HAVING子句确定是否需要为给定条件生成答案。

Let us consider the previous example.

让我们考虑前面的例子。

ID	Name	Marks	Section
1	Shiv	89	A
2	Parth	78	B
3	Ankush	95	A
4	Nimish	83	B

ID	名称	分数	部分
1个	希夫	89	一个
2	帕斯	78	乙
3	安库什	95	一个
4	尼米什人	83	乙

Question: Find the highest marks of the student for each section having marks greater than 90.

问题：对于分数大于90的每个部分，找出学生的最高分数。

SELECT MAX (S.Marks)
FROM Student S
GROUP BY S.Section
HAVING S.Marks > 90

Thus, our output will be:

因此，我们的输出将是：

ID	Name	Marks	Section
3	Ankush	95	A

ID	名称	分数	部分
3	安库什	95	一个

翻译自: https://www.includehelp.com/dbms/aggregate-operators-group-by-and-having-clause-in-dbms.aspx

dbms标识符无效