mysql 部门工资_mysql查询部门工资前三的信息

两个表，一个部门表Department

一个Employee表

第一种，(如果有多名并列第三，只展示前二)

SELECT

emp.id , emp.name ,emp.salary, dep.id as depId , dep.name as depName

FROM

Employee emp ,Employee emp1, Department dep

WHERE

emp.salary <=emp1.salary

AND emp.departmentId = emp1.departmentId

AND emp.departmentId = dep.id

GROUP BY

dep.id ,emp.id,emp.name ,emp.salary,dep.name

HAVING COUNT(*)<=3

ORDER BY

emp.departmentId , emp.salary desc;

查询结果

第二种(如果有多名并列第三，把所有第三名也展示出来)

Select d.Name as Department, e.Name as Employee, e.Salary

from Department d, Employee e

where DepartmentId = d.Id and (

Select count(distinct Salary) From Employee where DepartmentId=d.Id and Salary > e.Salary

)<3

order by Department,e.Salary DESC;

查询结果：

逐步分析第一种sql的执行过程

SELECT

emp.id , emp.name ,emp.salary, dep.id as depId , dep.name as depName

FROM

Employee emp ,Employee emp1, Department dep

WHERE

emp.salary <=emp1.salary

AND emp.departmentId = emp1.departmentId

AND emp.departmentId = dep.id

查询出第一个所有薪资低于第二个的情况

group聚合，把相同情况合并

SELECT

emp.id , emp.name ,emp.salary, dep.id as depId , dep.name as depName

FROM

Employee emp ,Employee emp1, Department dep

WHERE

emp.salary <=emp1.salary

AND emp.departmentId = emp1.departmentId

AND emp.departmentId = dep.id

GROUP BY

dep.id ,emp.id,emp.name ,emp.salary,dep.name

结果

找出三条记录

SELECT

emp.id , emp.name ,emp.salary, dep.id as depId , dep.name as depName

FROM

Employee emp ,Employee emp1, Department dep

WHERE

emp.salary <=emp1.salary

AND emp.departmentId = emp1.departmentId

AND emp.departmentId = dep.id

GROUP BY

dep.id ,emp.id,emp.name ,emp.salary,dep.name

HAVING COUNT(*)<=3

结果

按部门正序，薪资倒序排序

SELECT

emp.id , emp.name ,emp.salary, dep.id as depId , dep.name as depName

FROM

Employee emp ,Employee emp1, Department dep

WHERE

emp.salary <=emp1.salary

AND emp.departmentId = emp1.departmentId

AND emp.departmentId = dep.id

GROUP BY

dep.id ,emp.id,emp.name ,emp.salary,dep.name

HAVING COUNT(*)<=3

ORDER BY

emp.departmentId , emp.salary desc;

结果

附建表语句：

-- ----------------------------

-- Table structure for Department

-- ----------------------------

DROP TABLE IF EXISTS `Department`;

CREATE TABLE `Department` (

`Id` int(11) NOT NULL,

`Name` varchar(255) DEFAULT NULL,

PRIMARY KEY (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of Department

-- ----------------------------

INSERT INTO `Department` VALUES ('1', 'IT');

INSERT INTO `Department` VALUES ('2', 'Sales');

INSERT INTO `Department` VALUES ('3', 'free');

-- ----------------------------

-- Table structure for Employee

-- ----------------------------

DROP TABLE IF EXISTS `Employee`;

CREATE TABLE `Employee` (

`Id` int(11) NOT NULL AUTO_INCREMENT,

`Name` varchar(255) DEFAULT NULL,

`Salary` int(11) DEFAULT NULL,

`DepartmentId` int(11) DEFAULT NULL,

PRIMARY KEY (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of Employee

-- ----------------------------

INSERT INTO `Employee` VALUES ('1', 'Joe', '70000', '1');

INSERT INTO `Employee` VALUES ('2', 'Henry', '80000', '2');

INSERT INTO `Employee` VALUES ('3', 'Sam', '60000', '2');

INSERT INTO `Employee` VALUES ('4', 'Max', '90000', '1');

INSERT INTO `Employee` VALUES ('5', 'Janet', '85000', '1');

INSERT INTO `Employee` VALUES ('6', 'Randy', '85000', '1');

INSERT INTO `Employee` VALUES ('7', 'hshs', '4000000', '1');

INSERT INTO `Employee` VALUES ('8', 'ces', '60000', '2');