目录
一.建员工信息表u_info:
1.建表:
CREATE TABLE `u_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name1` varchar(255) DEFAULT NULL,
`depar_id` int(11) DEFAULT NULL,
`salary` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=99 DEFAULT CHARSET=utf8mb4;
2.插入员工数据:
INSERT INTO `mkmk_oor`.`u_info`(`name1`, `depar_id`, `salary`) VALUES ('李一', 1, '56000');
INSERT INTO `mkmk_oor`.`u_info`(`name1`, `depar_id`, `salary`) VALUES ('王二', 2, '56000');
INSERT INTO `mkmk_oor`.`u_info`(`name1`, `depar_id`, `salary`) VALUES ('王二', 2, '16000');
INSERT INTO `mkmk_oor`.`u_info`(`name1`, `depar_id`, `salary`) VALUES ('王海', 2, '55000');
INSERT INTO `mkmk_oor`.`u_info`(`name1`, `depar_id`, `salary`) VALUES ('王东', 3, '46000');
INSERT INTO `mkmk_oor`.`u_info`(`name1`, `depar_id`, `salary`) VALUES ('王南', 1, '52000');
INSERT INTO `mkmk_oor`.`u_info`(`name1`, `depar_id`, `salary`) VALUES ('王丁', 3, '76000');
INSERT INTO `mkmk_oor`.`u_info`(`name1`, `depar_id`, `salary`) VALUES ('王西', 2, '54000');
INSERT INTO `mkmk_oor`.`u_info`(`name1`, `depar_id`, `salary`) VALUES ('李山', 3, '56100');
INSERT INTO `mkmk_oor`.`u_info`(`name1`, `depar_id`, `salary`) VALUES ('王潢江', 2, '5000');
INSERT INTO `mkmk_oor`.`u_info`(`name1`, `depar_id`, `salary`) VALUES ('柳传志', 3, '51100');
INSERT INTO `mkmk_oor`.`u_info`(`name1`, `depar_id`, `salary`) VALUES ('习海', 2, '11000');
INSERT INTO `mkmk_oor`.`u_info`(`name1`, `depar_id`, `salary`) VALUES ('徐支', 1, '20000');
INSERT INTO `mkmk_oor`.`u_info`(`name1`, `depar_id`, `salary`) VALUES ('唐主', 1, '26000');
二.建部门表department:
1.建表:
CREATE TABLE `department` (
`id` int(11) NOT NULL,
`depart_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2.插入部门数据:
INSERT INTO `mkmk_oor`.`department`(`id`, `depart_name`) VALUES (1, '风控部');
INSERT INTO `mkmk_oor`.`department`(`id`, `depart_name`) VALUES (2, '运营部');
INSERT INTO `mkmk_oor`.`department`(`id`, `depart_name`) VALUES (3, '开发部');
三.表内容:
员工信息表内容: 部门表:
四.要求及实现:
1.要求:
要求:根据以上表查询出每个部门工资前三的员工信息和所属部门名
2.SQL如实现步骤下:
1).先用员工信息表一表多用,查询出工资前三的员工信息
SELECT a.*
from u_info a,u_info b
where b.depar_id=a.depar_id
and a.salary<=b.salary
GROUP BY
a.id,a.name1,a.depar_id,a.salary
HAVING count(*)<=3
ORDER BY
a.id,a.name1,a.depar_id,a.salary
查询如图:
2).将上述结果定义为表c,与部门表连接查询:
SELECT c.*,d.depart_name from
#这是上面sql的开始
(SELECT a.*
from u_info a,u_info b
where b.depar_id=a.depar_id and a.salary<=b.salary
GROUP BY
a.id,a.name1,a.depar_id,a.salary
HAVING count(*)<=3) c,
#这是sql的结束,重新定义为表c
department d
where c.depar_id=d.id
#下面的对结果进行排序一下。你不排序也可以,看你的个人爱好
ORDER BY c.depar_id,c.salary desc;
运行结果: