gpt4 book ai didi

mysql - 年薪月薪

转载 作者:行者123 更新时间:2023-11-29 03:20:13 25 4
gpt4 key购买 nike

我想从这样的表中找出工资差距最大的月份:

  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

但我卡住了,因为我只得到了时间间隔。有人对如何解决这个问题有什么建议吗?

最佳答案

创建一个只有两列的辅助表 salary_month:salarys_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 表,你会得到预期的结果。

关于mysql - 年薪月薪,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/46551919/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com