一,表
部门表
员工表
-- 创建表
DROP TABLE
IF
EXISTS `department`;
CREATE TABLE `department` (
`id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '主键',
`code` VARCHAR ( 100 ) DEFAULT NULL COMMENT '编号',
`name` VARCHAR ( 255 ) DEFAULT NULL COMMENT '名称',
PRIMARY KEY ( `id` ),
UNIQUE KEY `index_code` ( `code` ),
INDEX `index_name` ( `name` )
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '部门表';
DROP TABLE
IF
EXISTS `employees`;
CREATE TABLE `employees` (
`id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '主键',
`department_id` INT ( 11 ) NOT NULL COMMENT '部门ID',
`code` VARCHAR ( 100 ) DEFAULT NULL COMMENT '编号',
`name` VARCHAR ( 255 ) DEFAULT NULL COMMENT '名称',
PRIMARY KEY ( `id` ),
UNIQUE KEY `index_code` ( `code` ),
INDEX `department_id` ( `department_id` )
)ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '职工表';
二,测试
执行sql
SELECT t1.id, t1.name, t2.id emp_id, t2.department_id, t2.name emp_name FROM department t1 left join employees t2 on t1.id = t2.department_id
1,部门数据3条,员工2条,同属部门1
结果:4条数据
2,2个员工对应不同部门
结果:
3,6个员工分属6个不同部门,其中后三个部门不存在
结果:
4,6个人分属3个部门,其中两个属于同部门
结果:
5,查询部门人员大于1个人的部门ID
select department_id, count(*) total from employees WHERE 1=1 GROUP BY department_id HAVING count(*) > 1