SQL AVG函数_代码007(未授权)

SQL AVG函数是一个聚合函数，用于计算集合的平均值。以下说明了SQL AVG函数的语法：

AVG([ALL|DISTINCT] expression)

如果使用ALL关键字，AVG函数将获取计算中的所有值。默认情况下，无论是否指定，AVG函数都使用ALL。

如果明确指定DISTINCT关键字，AVG函数将仅在计算中采用唯一值。例如，有一组数据集(1,2,3,3,4)并将AVG(ALL)应用于此集合，AVG函数将执行以下计算：

(1+2+3+3+4)/5 = 2.6

但是，如果指定：AVG(DISTINCT)将按如下方式处理：

(1+2+3+4)/4 = 2.5

将使用示例数据库中的employees表来演示SQL AVG函数的工作原理。以下图片说明了employees表的结构：

mysql&gt; desc employees;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| employee_id   | int(11)      | NO   | PRI | NULL    | auto_increment |
| first_name    | varchar(20)  | YES  |     | NULL    |                |
| last_name     | varchar(25)  | NO   |     | NULL    |                |
| email         | varchar(100) | NO   |     | NULL    |                |
| phone_number  | varchar(20)  | YES  |     | NULL    |                |
| hire_date     | date         | NO   |     | NULL    |                |
| job_id        | int(11)      | NO   | MUL | NULL    |                |
| salary        | decimal(8,2) | NO   |     | NULL    |                |
| manager_id    | int(11)      | YES  | MUL | NULL    |                |
| department_id | int(11)      | YES  | MUL | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
10 rows in set

要计算所有员工的平均工资，请将AVG函数应用于salary列，如下所示：

SELECT 
    AVG(salary)
FROM
    employees;

+-------------+
| AVG(salary) |
+-------------+
| 8060        |
+-------------+
1 row in set

SELECT 
    AVG(DISTINCT salary)
FROM
    employees;

+----------------------+
| AVG(DISTINCT salary) |
+----------------------+
| 7845.454545          |
+----------------------+
1 row in set

SELECT 
    ROUND(AVG(DISTINCT salary), 2)
FROM
    employees;

SELECT
    AVG(DISTINCT salary)
FROM
    employees
WHERE
    department_id = 5;

SELECT 
    AVG(salary)
FROM
    employees
WHERE
    job_id = 6;

+-------------+
| AVG(salary) |
+-------------+
| 7920        |
+-------------+
1 row in set

SELECT
    department_id,
    AVG(salary)
FROM
    employees
GROUP BY
    department_id;

SELECT
    e.department_id,
    department_name,
    AVG(salary)
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
    e.department_id;

SELECT
    e.department_id,
    department_name,
    AVG(salary)
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
    e.department_id
ORDER BY
    AVG(salary) DESC;

SELECT
    e.department_id,
    department_name,
    AVG(salary) AS avgsalary
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
    e.department_id
HAVING avgsalary<5000
ORDER BY
    AVG(salary) DESC;

+---------------+-----------------+-----------+
| department_id | department_name | avgsalary |
+---------------+-----------------+-----------+
|             1 | 管理            | 4400      |
|             3 | 采购            | 4150      |
+---------------+-----------------+-----------+
2 rows in set

SELECT
    AVG(employee_sal_avg)
FROM
    (
        SELECT
            AVG(salary) employee_sal_avg
        FROM
            employees
        GROUP BY
            department_id
    ) t;