• 子查询


    # 子查询

    select * from student where borndate > (
    
    select borndate from student where stuname='崔今生'
    );

    /**
    先进行"子查询"后进行"父查询"
    **/

    ①先进行“子查询”

    select * from student where studentno =(
    select studentno from subject where studentresult =100 and subjectid=(
    select subjectid from result where subjectname='JavaSE'
    ));

    ②进行“父查询”

    select max(studentresult),min(studentresult) from result
    where subjectid=(
    select subjectid from subject where subjectname ='HTML和CSS课程'
    ) and examdate =(
    select max(examdate) from result where subjectid =(
    select subjectid from subject where subjectname ='HTML和CSS课程'
    )
    );

    # in子查询

    --查询"JavaSE"课程考试成绩不及格的学生名单

    select stuname from student where studnetno in (
    select studentno from result where studentresult<60 and subjectid=(
    select subjectid from subject where subjectname = 'JavaSE'
    ) 
    );

    # where子查询

    --查询员工编号最大的是谁?

    select * from s_emp where id =(
    select max(id) from s_emp
    );

    --查询最大的部门编号是谁?

    select * from s_emp where dept_id =(
    select max(dept_id) from s_emp
    )

    --查询公司所有的领导

    select * from s_emp where id in (
    select distinct manager_id from s_emp where mangager_id is not null
    )

    # having和from子查询

    --查询部门中平均工资大于32号部门平均工资的部门信息(显示部门编号和平均工资)

    select dept_id 部门编号,avg(salary)平均工资 from s_emp group by dept_id
    having avg(salary)>(
    select avg(salary) from s_emp where dept_id=32
    )


    --查询员工信息,条件为工资大于1400中,5号员工是谁?

    select * from (
    select * from s_emp where salary>1400 
    ) where id =5;


    --小结:三个关键字支持子查询:where.../ having.../ from...

  • 相关阅读:
    django core cache 永不失效配置
    zabbix-ODBC-oracle
    time
    zabbix中的触发器依赖
    Servlet1
    每周总结01
    使用IntelliJ IDEA集成TomCat
    hadoop环境配置
    《软件工程》学习进度条博客16
    03梦断代码读后感3
  • 原文地址:https://www.cnblogs.com/wzhqzm/p/13358070.html
Copyright © 2020-2023  润新知