月薪与年薪

Question

我想从这样的桌子上找出薪水差距最大的一个月：

  salary_table
      ID          Salary    fromdate     todate
       1            40000   1994-06-26   1995-06-26
       1            41000   1995-06-26   1996-06-25
       1            42000   1996-06-25   1996-06-25
       2            39000   1998-08-03   1999-08-03
       .
       . 

结果应该是最高月薪和最低月薪相差最大的月份。

我确信我目前的尝试是错误的：

SELECT MAX(`salary`) - MIN(`salary`) AS diff, 
YEAR(`from_date`), MONTH(`from_date`) FROM `salaries` 
GROUP BY  YEAR(`from_date`), MONTH(`from_date`)
ORDER BY diff DESC

但是我被困住了，因为我只得到了休息的机会。有人有办法解决这个问题吗？

Answer 1

创建一个只有两列的辅助表salary_month：salary和s_month。对于原始salary表中的每条记录，在salary_month中插入12条记录，在范围[fromdate, todate)中每个月插入一条记录。插入示例过程：

DELIMITER $$

create procedure get_month_salaries()
    MODIFIES SQL DATA   
begin 
    declare _df datetime;   
    declare _salary integer;
    declare _counter integer;
    declare done boolean default false;  
    declare cur cursor for select salary, fromdate from salaries;
    declare continue HANDLER for not found set done := true;

    open cur;

    test_loop: loop
        fetch cur into _salary, _df;
        if done then
          LEAVE test_loop;
        end if;

        set _counter = 0;
        while _counter < 12 do
           insert into salary_month values (_salary, DATE_ADD(_df, INTERVAL _counter MONTH));
           set _counter = _counter + 1;                 
        end while;
      end loop test_loop;
  close cur;          
end$$
DELIMITER ;

然后运行当前的查询。

SELECT MAX(`salary`) - MIN(`salary`) AS diff, 
YEAR(`s_month`), MONTH(`s_month`) FROM `salary_month` 
GROUP BY YEAR(`s_month`), MONTH(`s_month`)
ORDER BY diff DESC

在salary_month表上，您将得到预期的结果。