xiaozhengshuai
xiaozhengshuai
2021-03-12 17:07
采纳率: 25%
浏览 72
已采纳

这段sql语句怎么写?

从部门信息表dept,工资信息表salary和员工信息表employee中查询数据(输出信息包括员工ID,员工编号,员工姓名,所属部门,部门编号,员工工资),根据部门编号从高到低,工资从低到高列出每个员工的信息

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • niaonao
    niaonao 2021-03-12 18:04
    已采纳

     

    select 
     e.employee_id as '员工ID', e.employee_no as '员工编号', e.employee_name as '员工姓名',
     d.dept_name as '所属部门', d.dept_no as '部门编号', 
     s.salary_amount as '员工工资'
    from employee e 
    left join dept d on d.employee_id = e.employee_id
    left join salary s on s.employee_id = e.employee_id
    order by d.dept_no,s.salary_amount desc

    测试表结构

    CREATE TABLE `employee` (
        `employee_id` INT(9) NOT NULL AUTO_INCREMENT COMMENT '主键ID标识',
        `employee_no` INT(9) NOT NULL COMMENT '员工编号',
        `employee_name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '员工名称' COLLATE 'utf8mb4_general_ci',
        PRIMARY KEY (`employee_id`)
    )
    COMMENT='员工基础信息表'
    COLLATE='utf8mb4_general_ci'
    ENGINE=InnoDB
    ;
    
    CREATE TABLE `dept` (
        `dept_id` INT(9) NOT NULL AUTO_INCREMENT COMMENT '主键ID标识',
        `employee_id` INT(9) NOT NULL COMMENT '员工唯一标识',
        `dept_no` INT(9) NOT NULL COMMENT '部门编号',
        `dept_name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '部门名称' COLLATE 'utf8mb4_general_ci',
        PRIMARY KEY (`dept_id`)
    )
    COMMENT='部门基础信息表'
    COLLATE='utf8mb4_general_ci'
    ENGINE=InnoDB
    ;
    
    CREATE TABLE `salary` (
        `salary_id` INT(9) NOT NULL AUTO_INCREMENT COMMENT '主键ID标识',
        `employee_id` INT(9) NOT NULL COMMENT '员工唯一标识',
        `salary_amount` INT(9) NOT NULL COMMENT '薪资金额',
        PRIMARY KEY (`salary_id`)
    )
    COMMENT='薪资记录基础信息表'
    COLLATE='utf8mb4_general_ci'
    ENGINE=InnoDB
    ;
    点赞 评论
  • weixin_43574523
    菜鸟程序员大滔 2021-03-12 17:26

    表与表之间的对应关联字段是哪个

    点赞 评论
  • qq_36138652
    java-zh 2021-03-12 17:31

    除了这个题目应该还有其他条件吧?

    点赞 评论

相关推荐