最近有一个需求是这样的,查询用户的请假天数和需要写日志的天数。
数据如下:
DROP TABLE IF EXISTS `test2`;
CREATE TABLE `test2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL DEFAULT '0',
`date` date NOT NULL,
`type` tinyint(2) NOT NULL DEFAULT '0' COMMENT '请假类型 0=全天 1=上午 2=下午',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test2` (`id`, `uid`, `date`, `type`) VALUES (1, 40, '2022-05-16', 0);
INSERT INTO `test2` (`id`, `uid`, `date`, `type`) VALUES (2, 40, '2022-05-17', 0);
INSERT INTO `test2` (`id`, `uid`, `date`, `type`) VALUES (3, 40, '2022-05-18', 0);
INSERT INTO `test2` (`id`, `uid`, `date`, `type`) VALUES (4, 40, '2022-05-19', 0);
INSERT INTO `test2` (`id`, `uid`, `date`, `type`) VALUES (5, 40, '2022-05-31', 2);
INSERT INTO `test2` (`id`, `uid`, `date`, `type`) VALUES (6, 40, '2022-05-31', 1);
INSERT INTO `test2` (`id`, `uid`, `date`, `type`) VALUES (7, 40, '2022-05-20', 1);
');
需求一:查询请假天数
根据表中数据,我们需要查出用户id为40的用户在五月份一共请假多少天。其中类型type 0=全天(即请假一天)1=上午 2=下午
我们可以使用以下SQL查询:
SELECT
*,
( CASE WHEN type = '0' THEN 1 WHEN type = "1" THEN 0.5 WHEN type = "2" THEN 0.5 END ) time_long
FROM
test2
WHERE
date BETWEEN '2022-05-01'
AND '2022-05-31';
得到:
其中,time_long即为请假时长,半天记为0.5,一天记为1。
然后我们可以很轻松的算出该用户在该时间段内的请假时长。我们使用sum进行子查询。
SELECT
SUM( time_long ) AS time_long
FROM
(
SELECT
*,
( CASE WHEN type = '0' THEN 1 WHEN type = "1" THEN 0.5 WHEN type = "2" THEN 0.5 END ) time_long
FROM
test2
WHERE
date BETWEEN '2022-05-01'
AND '2022-05-31'
) a
得到:
到此时我们第一个需求就算完成啦。
需求二:查询应写日志数量
日志数量的计算方式为(以五月份为例):五月份实际天数为:31天,除去节假日和周六日外实际上班天数为:20天。这里我们可以通过节假日等API接口计算获取时长天数。
假设:我们通过接口获取到了该时间范围内的实际上班天数:20天。然后请假一天不写日志,请假半天需要写日志
。
我们根据现有数据,可以得到,该用户需要写日志天数为:15天
注意:这里的5月31日合在一起请了一天假,所以5月31日不用写日志。5月20日需要写日志
。
这里推荐一个节假日API
:
节假日API
然后在这里我们就需要用到一个向下取整的函数 FLOOR
SELECT
FLOOR(
SUM( time_long )) AS time_long
FROM
(
SELECT
date,
SUM( time_long ) AS time_long
FROM
(
SELECT
*,
( CASE WHEN type = '0' THEN 1 WHEN type = "1" THEN 0.5 WHEN type = "2" THEN 0.5 END ) time_long
FROM
test2
WHERE
date BETWEEN '2022-05-01'
AND '2022-05-31'
) a
GROUP BY
date
) b;
反向查询得到不应该写日志的天数:5天。
最后通过代码计算得到应写日志天数:20 - 5 = 15天
以上欢迎各位点评指正。