这段sql语句怎么写?

Question

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

Answer 1

 

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
;