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');