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

ゞ浴缸里的玫瑰 2022-01-26 01:53 281阅读 0赞

两个表，一个部门表Department

![20190527210348525.png][]

一个Employee表

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70][]

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

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;

查询结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70 1][]

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

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;

查询结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70 2][]

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70 3][]

group聚合，把相同情况合并

结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70 4][]

找出三条记录

结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70 5][]

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

结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70 6][]

附建表语句：

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

[20190527210348525.png]: /images/20220126/7a26cd0a106a4eddad48a90c6ba56e83.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70]: /images/20220126/19f4205529f34310a25a9bc8ea9c7499.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70 1]: /images/20220126/2f7b2073b7114066a164915c2604b8cf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70 2]: /images/20220126/3decb7b91426474eb676757ac8c33763.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70 3]: /images/20220126/c0a5b282d80f4dfb8dd65b739aea6666.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70 4]: /images/20220126/a97f1f8fc5924276bd94ee8af62d15d3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70 5]: /images/20220126/7bbaf148615f46269664431eb8966285.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70 6]: /images/20220126/f672dfe1e69e41aaac6a8dfffc7f27d2.png