1. 首页首页
  2. 编程日记编程日记

聚合函数不可以用在select语句的下列哪个子句中（聚合函数不可以用在select语句的下列哪个子句中进行）

编程日记 • • 阅读 35

聚合函数不可以用在select语句的下列哪个子句中（聚合函数不可以用在select语句的下列哪个子句中进行）1 查询和 Zlotkey 相同部门的员工姓名和工资 1 分析题 找出定语 查询和 Zlotkey 相同部门 一般 的 前面的做定于修饰后面的句子 2 写出定语的查询语句 SELECT department id FROM employees WHERE last name Zlotkey 找出了 Zlotkey 部门 ID 3 完成题目 SELECT last name salary FROM employees WHERE department id



#1.查询和Zlotkey相同部门的员工姓名和工资
-- 1.分析题,找出定语"查询和Zlotkey相同部门"(一般"的"前面的做定于修饰后面的句子)
-- 2.写出定语的查询语句SELECT department_id FROM employees WHERE last_name = 'Zlotkey'找出了Zlotkey部门ID
-- 3.完成题目
SELECT
  last_name,
  salary 
FROM
  employees 
WHERE
  department_id = ( SELECT department_id FROM employees WHERE last_name = 'Zlotkey' );


#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
-- 1.分析题,找出定语"工资比公司平均工资高"
-- 2.写出定语的查询语句salary > (SELECT AVG(salary) avg_emp FROM employees)
-- 3.完成题目
SELECT 
  employee_id,
  last_name,
  salary
FROM
  employees
WHERE
  salary > (SELECT AVG(salary) avg_emp FROM employees)
  

#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
-- 1.分析题,找出定语"工资大于所有JOB_ID = 'SA_MAN'的员工"
-- 2.写出定语的查询语句salary > ALL (SELECT salary FROM employees WHERE JOB_ID = 'SA_MAN')
-- 3.完成题目
SELECT 
  last_name,
  job_id,
  salary
FROM
  employees
WHERE
  salary > ALL (SELECT salary FROM employees WHERE job_id = 'SA_MAN')


#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
-- 1.分析题,找出定语"姓名中包含字母u的员工在相同部门的员工"
-- 2.写出定语的查询语句SELECT department_id,last_name FROM employees WHERE last_name LIKE '큛r />-- 3.完成题目
SELECT
  employee_id,
  last_name
FROM
  employees
WHERE
  (department_id,last_name) IN (SELECT department_id,last_name FROM employees WHERE last_name LIKE '캂;

#5.查询在部门的location_id为1700的部门工作的员工的员工号
-- 1.分析题,找出定语"部门的location_id为1700的部门工作的员工"
-- 2.写出定语的查询语句SELECT department_id FROM departments WHERE location_id = 1700
-- 3.完成题目
SELECT
  employee_id
FROM
  employees
WHERE
  department_id IN (SELECT department_id FROM departments WHERE location_id = 1700)


#6.查询管理者是King的员工姓名和工资
-- 1.分析题,找出定语"管理者是King"
-- 2.写出定语的查询语句SELECT employee_id FROM employees WHERE last_name = 'King'
-- 3.完成题目
SELECT
  last_name,
  salary
FROM
  employees
WHERE
  manager_id IN (SELECT employee_id FROM employees WHERE last_name = 'King')


#7.查询工资最低的员工信息: last_name, salary
-- 1.分析题,找出定语"工资最低"
-- 2.写出定语的查询语句SELECT MIN(salary) FROM employees
-- 3.完成题目
SELECT
  last_name,
  salary
FROM
  employees
WHERE
  salary = (SELECT MIN(salary) FROM employees)

#8.查询平均工资最低的部门信息
-- 1.分析题,找出定语"平均工资最低的部门"
-- 2.写出定语的查询语句
-- SELECT MIN( avg_salary ) FROM ( SELECT AVG( salary ) avg_salary FROM employees GROUP BY department_id ) t_avg_salary
-- 3.找到平均工资后,在把这个平均工资当成一个新表来用即可.需要注意的点就是要给新表起别名,要给平均薪资字段起别名
-- 方式一:先求出平均工资,在求最低工资,因为前两次求的是平均工资最低的数据,所以最后条件要用AVG函数来接收
SELECT
  *
FROM
  departments
WHERE
  department_id = (
    SELECT
      department_id 
    FROM
      employees 
    GROUP BY
      department_id 
    HAVING
      AVG( salary ) = ( SELECT MIN( avg_salary ) FROM ( SELECT AVG( salary ) avg_salary FROM employees GROUP BY department_id ) t_avg_salary )
  )


-- 方式二:求出各部门最低工资后,直接设置条件让它等于查出结果中最小的那个即可
SELECT
  *
FROM
  departments
WHERE
  department_id = (
    SELECT
      department_id
    FROM
      employees
    GROUP BY
      department_id 
    HAVING
      AVG( salary ) <= ALL ( SELECT AVG( salary ) avg_salary FROM employees GROUP BY department_id )
  )


-- 方式三:求出各部门平均工资后,进行升序排序,然后通过分页查询取第一条数据即可
SELECT
  * 
FROM
  departments 
WHERE
  department_id = (
    SELECT
      department_id 
    FROM
      employees 
    GROUP BY
      department_id 
    HAVING
      AVG( salary )=(
        SELECT
          AVG( salary ) avg_salary 
        FROM
          employees 
        GROUP BY
          department_id 
        ORDER BY
          avg_salary 
          LIMIT 0,
          1 
        ));

-- 方式四
-- 查询出各部门平均工资后,把数据当成新表来用,与旧表进行自连接
SELECT
  d.*
FROM
  departments d,(
    SELECT department_id,AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id
    ORDER BY avg_sal
    LIMIT 0,1) t_avg_sal
WHERE d.department_id = t_avg_sal.department_id



#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
-- 方式一：
-- 1.这里部门信息采用子查询查到的平均工资最低的数值
-- 2.通过平均工资最低的数值在进行查询到部门ID
-- 3.根据部门ID获取部门信息
-- 4.平均工资通过在select语句中在进行相关子查询，获得该部门的平均工资
-- 这种方式呢，是通过平均工资最低数值来获取部门ID，其实部门ID是可以直接获取的，这里相当于多走一步
SELECT
  d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) avg_salary
FROM
  departments d
WHERE
  department_id = (
    SELECT
      department_id
    FROM
      employees
    GROUP BY
      department_id 
    HAVING
      AVG( salary ) <= ALL ( SELECT AVG( salary ) avg_salary FROM employees GROUP BY department_id )
  )

-- 方式二：
-- 在方式一基础上，获取最低工资的方式不在是和全部部门的工资作比较，而是直接让工资等于升序过后的第一个工资
SELECT d.*,( SELECT AVG( salary ) FROM employees WHERE department_id = d.department_id ) avg_salary 
FROM
  departments d 
WHERE
  department_id = (
  SELECT
    department_id 
  FROM
    employees 
  GROUP BY
    department_id 
  HAVING
    AVG( salary ) = ( SELECT AVG( salary ) avg_salary FROM employees GROUP BY department_id ORDER BY avg_salary LIMIT 0, 1 ) 
  );
  
-- 方式三：
-- 1.通过MIN聚合函数来获取最低平均工资
-- 2.然后在筛选平均工资为最低平均工资的部门
-- 3.获取部门信息....
SELECT d.*,( SELECT AVG( salary ) FROM employees WHERE department_id = d.department_id ) avg_salary 
FROM
  departments d 
WHERE
  department_id = (
  SELECT
    department_id 
  FROM
    employees 
  GROUP BY
    department_id 
  HAVING
  AVG( salary ) = ( SELECT MIN( avg_salary ) FROM ( SELECT AVG( salary ) avg_salary FROM employees GROUP BY department_id ) t_avg_salary ));


-- 方式四：
-- 平均工资最低的部门
SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 0,1
-- 部门信息和该部门的平均工资
-- 1.这里部门信息采用子查询查到的平均工资最低的部门ID来获取部门信息
-- 2.平均工资通过在select语句中在进行相关子查询，获得该部门的平均工资
SELECT d.*,( SELECT AVG( salary ) FROM employees e WHERE d.department_id = e.department_id GROUP BY department_id ) avg_sala
FROM
  departments d 
WHERE
  department_id = ( SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG( salary ) LIMIT 0, 1 );

-- 方式五：
-- 效率最高,通过自连接
SELECT d.*,t_avg_sal.avg_sal
FROM  departments d,(
  SELECT
    department_id,
    AVG(salary) avg_sal
  FROM
    employees 
  GROUP BY
    department_id 
  ORDER BY
    AVG( salary ) 
    LIMIT 0,1) t_avg_sal
WHERE d.department_id = t_avg_sal.department_id



#10.查询平均工资最高的 job 信息
-- 1.分析题,找出定语"平均工资最高"
-- 2.写出定语的查询语句AVG( salary ) >= ALL(SELECT AVG( salary ) avg_salary FROM employees GROUP BY job_id)
-- 3.完成题目
SELECT
  j.* 
FROM
  jobs j,
  ( SELECT job_id, AVG( salary ) avg_salary FROM employees GROUP BY job_id ORDER BY avg_salary DESC LIMIT 0, 1 ) t_avg_salary 
WHERE
  j.job_id = t_avg_salary.job_id;

#11.查询平均工资高于公司平均工资的部门有哪些?
-- 1.分析题,找出定语"平均工资高于公司平均工资的部门"
-- 2.写出定语的查询语句AVG(salary) > (SELECT AVG(salary) FROM employees)
-- 3.完成题目
SELECT
  department_id
FROM
  employees
WHERE
  department_id IS NOT NULL
GROUP BY
  department_id
HAVING
  AVG(salary) > (SELECT AVG(salary) FROM employees)



#12.查询出公司中所有 manager 的详细信息
-- 1.分析题,找出定语"公司中所有 manager "
-- 2.写出定语的查询语句employee_id IN (SELECT DISTINCT manager_id FROM employees)
-- 3.完成题目
SELECT
  employee_id,
  last_name,
  salary
FROM
  employees
WHERE
  employee_id IN (SELECT DISTINCT manager_id FROM employees)
  
SELECT
  employee_id,
  last_name,
  salary
FROM
  employees e1
WHERE
  EXISTS (SELECT * FROM employees e2 WHERE e2.manager_id = e1.employee_id)


-- 自连接
SELECT
  e1.employee_id,
  e1.last_name,
  e1.salary
FROM
  employees e1 JOIN employees e2
WHERE
  e1.employee_id = e2.manager_id
  
  

#13.各个部门中最高工资中最低的那个部门的 最低工资是多少?
-- 1.分析题,找出定语"各个部门中最高工资中最低的那个部门 "
-- 2.写出定语的查询语句salary <= ALL(SELECT MAX(salary) FROM employees GROUP BY department_id)
-- 3.完成题目
SELECT
  MIN(salary)
FROM
  employees
WHERE
  salary <= ALL(SELECT MAX(salary) FROM employees GROUP BY department_id)
  
-- 自连接
SELECT
  salary
FROM
  employees e1,(SELECT department_id,MAX(salary) max_salary FROM employees GROUP BY department_id ORDER BY max_salary LIMIT 0,1
) t_max_salay
WHERE
  e1.department_id = t_max_salay.department_id


#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
-- 1.分析题,找出定语"平均工资最高的部门 "
-- 2.写出定语的查询语句salary >= ALL(SELECT AVG(salary) FROM employees GROUP BY department_id)获取最高部门的ID
-- 3.获得所有manager的信息
-- 4.设置限制条件进行匹配
SELECT
  last_name, 
  department_id, 
  email, 
  salary
FROM
  employees
WHERE
  employee_id IN (SELECT DISTINCT manager_id FROM employees)
  AND department_id = (SELECT department_id FROM employees WHERE salary>=ALL (
SELECT AVG(salary) FROM employees GROUP BY department_id))
  

-- 平均工资最高的部门
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal DESC
LIMIT 0,1

-- 自连接方式
SELECT *
FROM employees
WHERE employee_id IN (SELECT DISTINCT manager_id
FROM employees e,(
                  SELECT department_id,AVG(salary) avg_sal
                  FROM employees
                  GROUP BY department_id
                  ORDER BY avg_sal DESC
                  LIMIT 0,1
                  )t_avg_sal
WHERE
  e.department_id = t_avg_sal.department_id)

#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
-- 这里注意,题目说的是部门中的部门号,不是员工表中的部门号,所以也要进行子查询
SELECT
  department_id
FROM
  departments
WHERE
  department_id NOT IN (
      SELECT DISTINCT
        department_id 
      FROM
        employees 
      WHERE
        job_id = 'ST_CLERK'
  )


#16. 选择所有没有管理者的员工的last_name
SELECT
  last_name
FROM
  employees
WHERE
  manager_id IS NULL


#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
SELECT
  employee_id,
  last_name,
  hire_date,
  salary
FROM
  employees
WHERE
  manager_id = (SELECT employee_id FROM employees WHERE last_name = 'De Haan')


#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
-- 1.分析题,找出定语"各部门中工资比本部门平均工资高的员工 "
-- 2.写出定语的查询语句SELECT AVG( salary ) FROM employees e2 WHERE e2.department_id = e1.department_id GROUP BY department_id 
-- 3.设置限制条件进行匹配
SELECT
  e1.employee_id,
  e1.last_name,
  e1.salary 
FROM
  employees e1 
WHERE
  salary > ( SELECT AVG( salary ) FROM employees e2 WHERE e2.department_id = e1.department_id GROUP BY department_id  );

#19.查询每个部门下的部门人数大于 5 的部门名称
-- 1.分析题,找出定语"查询每个部门下的部门人数大于 5  "
-- 2.写出定语的查询语句5 < (SELECT COUNT(*) FROM employees e2 WHERE e2.department_id = e1.department_id )
-- 3.设置限制条件进行匹配
SELECT DISTINCT
  department_name
FROM
  employees e1
WHERE
  5 < (SELECT COUNT(*) FROM employees e2 WHERE e2.department_id = e1.department_id )

#20.查询每个国家下的部门个数大于 2 的国家编号
-- 1.分析题,找出定语"查询每个国家下的部门个数大于2"
-- 2.写出定语的查询语句2 < (SELECT COUNT(*) FROM departments d WHERE l.location_id=d.location_id)
-- 3.设置限制条件进行匹配
SELECT
  country_id
FROM
  locations l
WHERE
  2 < (
    SELECT COUNT(*)
    FROM departments d
    WHERE l.location_id = d.location_id
  )
编程小号
上一篇 2025-03-05 12:46
下一篇 2025-03-08 22:30

相关推荐

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/bian-cheng-ri-ji/14508.html