• 大数据第61天—MySQL之员工累计薪水-杨大伟


    需求:查询一个员工三个月内的累计薪水,但是不包括最近一个月的薪水。

    展示效果:

    IdMonthSalary
    1 3 90
    1 2 50
    1 1 20
    2 1 20
    3 3 100
    3 2 40
     1 Create table If Not Exists 19_Employee (Id int, Month int, Salary int);
     2 Truncate table 19_Employee;
     3 insert into 19_Employee (Id, Month, Salary) values (1, 1, 20);
     4 insert into 19_Employee (Id, Month, Salary) values (2, 1, 20);
     5 insert into 19_Employee (Id, Month, Salary) values (1, 2, 30);
     6 insert into 19_Employee (Id, Month, Salary) values (2, 2, 30);
     7 insert into 19_Employee (Id, Month, Salary) values (3, 2, 40);
     8 insert into 19_Employee (Id, Month, Salary) values (1, 3, 40);
     9 insert into 19_Employee (Id, Month, Salary) values (3, 3, 60);
    10 insert into 19_Employee (Id, Month, Salary) values (1, 4, 60);
    11 insert into 19_Employee (Id, Month, Salary) values (3, 4, 70);

    说明:员工 1 除去最近一个月(月份 4),有三个月的薪水记录:月份 3 薪水为 40,月份 2 薪水为 30,月份 1 薪水为 20。所以近 3 个月的薪水累计分别为 (40 + 30 + 20) = 90,(30 + 20) = 50 和 20。

    最终SQL:

     1 SELECT
     2     E1.id,
     3     E1.month,
     4     (IFNULL(E1.salary, 0) + IFNULL(E2.salary, 0) + IFNULL(E3.salary, 0)) AS Salary
     5 FROM
     6     (SELECT
     7         id, MAX(month) AS month
     8     FROM
     9         19_Employee
    10     GROUP BY 
    11         id
    12     HAVING 
    13         COUNT(*) > 1) AS maxmonth
    14     LEFT JOIN
    15         19_Employee E1 
    16     ON 
    17         (maxmonth.id = E1.id AND maxmonth.month > E1.month)
    18     LEFT JOIN
    19         19_Employee E2 
    20     ON 
    21         (E2.id = E1.id AND E2.month = E1.month - 1)
    22     LEFT JOIN 
    23         19_Employee E3 
    24     ON
    25         (E3.id = E1.id AND E3.month = E1.month - 2)
    26 ORDER BY 
    27     id ASC , month DESC;
  • 相关阅读:
    第1次作业
    第0次作业
    总结报告
    第14、15周作业
    第七周作业
    第六周作业
    第四周作业
    第四次作业
    第三次作业
    2018第二次作业
  • 原文地址:https://www.cnblogs.com/shui68home/p/13736180.html
Copyright © 2020-2023  润新知