mysql - MySQL - 计算每个员工每月的收入

Question

我试图从我的表“每位员工的收入”（每位员工为公司产生的收入）每月计算。

我的代码到目前为止：

SELECT  
    y.yr,
    d.details,
    d.labelname,
    sum(case when month(app_date) = 1  then val else 0 end) month_01,
    sum(case when month(app_date) = 2  then val else 0 end) month_02,
    sum(case when month(app_date) = 3  then val else 0 end) month_03,
    sum(case when month(app_date) = 4  then val else 0 end) month_04,
    sum(case when month(app_date) = 5  then val else 0 end) month_05,
    sum(case when month(app_date) = 6  then val else 0 end) month_06,
    sum(case when month(app_date) = 7  then val else 0 end) month_07,
    sum(case when month(app_date) = 8  then val else 0 end) month_08,
    sum(case when month(app_date) = 9  then val else 0 end) month_09,
    sum(case when month(app_date) = 10 then val else 0 end) month_10,
    sum(case when month(app_date) = 11 then val else 0 end) month_11,
    sum(case when month(app_date) = 12 then val else 0 end) month_12,
    sum(case when month(app_date) > 0  then val else 0 end) total
from (
    select 'a' dorder,'Peter' labelname,'1' details union all 
    select 'b' dorder,'John + Mary' labelname,'2' details union all
    select 'c' dorder,'John' labelname,'3' details 
) d cross join (
    select distinct year(app_date) yr
    from tblapp 
) y    
left join (  
       SELECT app_date, COALESCE(app_price, 0) val, '1' details from tblapp 
       INNER JOIN tblemployee ON tblemployee.emp_id = tblapp.emp_id 
       where tblemployee.emp_id=1   
    union all
       SELECT app_date, COALESCE(app_price, 0) val, '2' details from tblapp 
       INNER JOIN tblemployee ON tblemployee.emp_id = tblapp.emp_id 
       INNER JOIN tblassistant ON tblassistant.ass_id = tblapp.ass_id
       where tblemployee.emp_id=2 AND tblassistant.ass_id=1
    union all
       SELECT app_date, COALESCE(app_price, 0) val, '3' details from tblapp 
       INNER JOIN tblemployee ON tblemployee.emp_id = tblapp.emp_id 
       where tblemployee.emp_id=2       
) t on year(t.app_date) = y.yr and t.details = d.details
group by y.yr, d.details
order by y.yr desc, d.dorder;

代码工作正常。唯一的问题是当我添加新员工或助理时，我必须编辑代码并手动创建 WHERE 条件（员工和助理之间的组）

CREATE TABLE `tblapp` (
      `app_id` smallint(5) UNSIGNED NOT NULL,
      `app_date` date DEFAULT NULL,
      `app_price` double DEFAULT NULL,
      `emp_id` smallint(5) UNSIGNED DEFAULT NULL,
      `ass_id` smallint(5) UNSIGNED DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO `tblapp` (`app_id`, `app_date`, `app_price`, `emp_id`, `ass_id`) VALUES
    (1, '2021-01-04', 100, 1, NULL),
    (2, '2021-01-29', 100, 5, 1),
    (3, '2021-02-20', 100, 2, 1),
    (4, '2021-02-02', 100, 3, 2),
    (5, '2021-03-19', 100, 2, NULL),
    (6, '2021-04-24', 100, 4, 2),
    (7, '2021-05-09', 100, 1, 1),
    (8, '2021-07-04', 100, 2, 2),
    (9, '2021-09-18', 100, 3, 1),
    (10, '2021-10-12', 100, 5, NULL);
    
    CREATE TABLE `tblemployee` (
      `emp_id` smallint(5) UNSIGNED NOT NULL,
      `emp_name` varchar(50) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO `tblemployee` (`emp_id`, `emp_name`) VALUES
    (1, 'Peter'),
    (2, 'John'),
    (3, 'Alex'),
    (4, 'Stack'),
    (5, 'Over'),
    (6, 'Flow');
    
    CREATE TABLE `tblassistant` (
      `ass_id` smallint(5) UNSIGNED NOT NULL,
      `ass_name` varchar(50) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO `tblassistant` (`ass_id`, `ass_name`) VALUES
    (1, 'Mary'),
    (2, 'Andrew'),
    (3, 'John'),
    (4, 'Helen');

http://sqlfiddle.com/#!9/3fd588/3

标签： mysql