mysql - 按比例正确汇总休假时间 - mysql

coder 2023-10-13 原文

在 mysql 数据库中，我创建了“离开”表:

CREATE TABLE `leave` ( 
`ID_LEAVE` int(11) NOT NULL,
`ID_WORKER` int(11) NOT NULL,
`BEGIN_DATE` datetime DEFAULT NULL,
`END_DATE` datetime DEFAULT NULL
);

INSERT INTO `leave` 
(`ID_LEAVE`, `ID_WORKER`, `BEGIN_DATE`, `END_DATE`) VALUES
(3, 26, '2019-03-20 07:00:00', '2019-03-21 15:00:00'),
(4, 22, '2019-03-20 07:00:00', '2019-03-20 15:00:00');

“ worker ”表:

CREATE TABLE `workers` (
`ID_WORKER` int(11) NOT NULL,
`FNAME` varchar(20) NOT NULL,
`LNAME` varchar(20) NOT NULL
);

INSERT INTO `workers` (`ID_WORKER`, `FNAME`, `LNAME`) VALUES
(22, 'ALAN', 'FAST'),
(23, 'LEON', 'SPEED'),
(24, 'ADAM', 'GREEN'),
(25, 'DAVID', 'BUCS'),
(26, 'JACK', 'FAR'),
(27, 'ADAM', 'GAX'),
(28, 'ANDREW', 'WORM');

“订单”表:

CREATE TABLE `orders` (
`ID_ORDER` int(11) NOT NULL,
`DESC_ORDER` varchar(50) NOT NULL,
`NUMBER_ORDER` varchar(30) NOT NULL
);

INSERT INTO `orders` (`ID_ORDER`, `DESC_ORDER`, `NUMBER_ORDER`) VALUES
(19, 'TEST', 'TEST'),
(20, 'TEST2', 'TEST2'),
(21, 'TEST3', 'TEST3'),
(22, 'TEST4', 'TEST4');

“Order_status”表(抱歉，顺序不对):

CREATE TABLE `order_status` (
`ID_STATUS` int(11) NOT NULL,
`ID_ORDER` int(11) NOT NULL,
`ID_WORKER` int(11) NOT NULL,
`BEGIN_DATE` datetime NOT NULL,
`END_DATE` datetime NOT NULL,
`ORDER_DONE` varchar(50) DEFAULT NULL
) ;

INSERT INTO `order_status` (`ID_STATUS`, `ID_ORDER`, `ID_WORKER`, `BEGIN_DATE`, `END_DATE`, `ORDER_DONE`) VALUES
(30, 19, 26, '2019-03-18 06:40:21', '2019-03-18 15:22:32', 'NO'),
(31, 19, 25, '2019-03-18 06:51:23', '2019-03-18 15:11:10', 'NO'),
(32, 19, 26, '2019-03-20 06:23:32', '2019-03-20 15:33:11', 'NO'),
(33, 19, 25, '2019-03-20 06:44:42', '2019-03-20 15:16:22', 'NO'),
(34, 19, 26, '2019-03-22 06:44:32', '2019-03-22 11:44:54', 'YES'),
(35, 19, 25, '2019-03-22 06:59:22', '2019-03-22 12:33:33', 'YES'),
(42, 20, 22, '2019-03-18 06:44:23', '2019-03-18 15:11:23', 'NO'),
(43, 20, 23, '2019-03-18 06:50:45', '2019-03-18 15:15:44', 'NO'),
(44, 20, 22, '2019-03-19 06:40:20', '2019-03-19 15:23:30', 'NO'),
(45, 20, 22, '2019-03-21 06:55:45', '2019-03-21 16:03:23', 'NO'),
(46, 20, 22, '2019-03-22 06:55:45', '2019-03-22 13:23:44', 'YES'),
(47, 20, 23, '2019-03-19 06:45:23', '2019-03-19 15:33:23', 'NO'),
(48, 20, 23, '2019-03-20 06:45:23', '2019-03-20 15:22:23', 'NO'),
(49, 20, 23, '2019-03-21 06:50:33', '2019-03-21 15:33:11', 'NO'),
(50, 20, 23, '2019-03-22 06:44:23', '2019-03-22 12:22:44', 'YES'),
(51, 21, 24, '2019-03-18 06:46:55', '2019-03-18 15:23:22', 'NO'),
(52, 21, 24, '2019-03-19 06:55:33', '2019-03-19 15:23:33', 'NO'),
(53, 21, 24, '2019-03-20 06:50:59', '2019-03-20 15:44:02', 'NO'),
(54, 21, 24, '2019-03-21 06:44:33', '2019-03-21 15:23:33', 'YES'),
(55, 21, 24, '2019-03-22 06:49:33', '2019-03-22 11:22:32', 'YES'),
(56, 22, 27, '2019-03-18 06:50:12', '2019-03-18 15:22:15', 'NO'),
(57, 22, 28, '2019-03-18 06:44:12', '2019-03-18 15:33:44', 'NO'),
(58, 22, 27, '2019-03-19 06:53:22', '2019-03-19 15:22:33', 'NO'),
(59, 22, 28, '2019-03-19 06:33:22', '2019-03-19 15:15:22', 'NO'),
(60, 22, 28, '2019-03-21 06:33:22', '2019-03-21 15:01:56', 'NO'),
(61, 22, 27, '2019-03-22 06:33:22', '2019-03-22 11:05:33', 'YES'),
(62, 22, 27, '2019-03-21 06:45:22', '2019-03-21 15:33:33', 'NO'),
(63, 22, 28, '2019-03-22 06:51:33', '2019-03-22 10:35:55', 'YES'),
(64, 19, 25, '2019-03-19 06:50:32', '2019-03-19 15:33:44', 'NO'),
(65, 19, 26, '2019-03-19 06:44:50', '2019-03-19 15:22:33', 'NO'),
(66, 19, 25, '2019-03-21 06:50:50', '2019-03-21 15:33:33', 'NO'),
(67, 22, 27, '2019-03-20 06:51:32', '2019-03-20 15:20:33', 'NO');

我做了什么:

我可以总结每个其他 worker 的“订单时间”。我也正确地选择了 worker (LNAME、FNAME)订单(DESC_ORDER 和 NUMBER_ORDER)和其他 worker 订购的“TOTAL TIME”。我在下面写了 mysql 命令:

SELECT workers.FNAME, 
workers.LNAME, 
order_statusAgg.NUMBER_ORDER,
order_statusAgg.DESC_ORDER, 
SEC_TO_TIME(SUM(order_statusAgg.stime)) AS 'ORDER TIME'
FROM workers
LEFT JOIN (
SELECT order_status.ID_WORKER, orders.NUMBER_ORDER, orders.DESC_ORDER, 
SUM((TIME_TO_SEC(order_status.END_DATE) - TIME_TO_SEC(order_status.BEGIN_DATE))) AS stime 
FROM order_status INNER JOIN orders
ON orders.ID_ORDER = order_status.ID_ORDER
GROUP BY order_status.ID_WORKER) order_statusAgg
ON workers.ID_WORKER = order_statusAgg.ID_WORKER
WHERE order_statusAgg.NUMBER_ORDER LIKE 'TEST'
GROUP BY workers.ID_WORKER;

然后我得到:

+---------+---------+---------------+------------+------------+
|  FNAME  | LNAME   |  NUMBER_ORDER | DESC_ORDER | ORDER TIME | 
+---------+---------+---------------+------------+------------+
|  DAVID  |  BUCS   | TEST          | TEST       | 22:30:21   |
+---------+---------+---------------+------------+------------+
|  JACK   |  FAR    | TEST          | TEST       | 21:19:18   |
+---------+---------+---------------+------------+------------+

我分析了 2 天(2019 年 3 月 20 日和 2019 年 3 月 21 日)。照片如下:

现在我需要 mysql 查询来正确和按比例汇总离开时间以符合下面描述的模式:

例如 ID_WORKER = 26 的休假时间(他有订单“TEST”)

                    '08:31:40'                                      '08:31:40'
(-------------------------------------------------) * 8:00:00 =  (--------------) * 8:00:00 = 2:00:16
 '08:31:40' + '08:37:00' + '08:53:03' + '08:29:01'                  '34:30:44'

我试过的方法:我试过在下面通过 mysql 查询求和休假时间:

SELECT workers.FNAME, 
workers.LNAME, 
order_statusAgg.NUMBER_ORDER,
order_statusAgg.DESC_ORDER, 
SEC_TO_TIME(SUM(order_statusAgg.stime)) AS 'ORDER TIME', 
IFNULL(SEC_TO_TIME(SUM(leaveAgg.ltime)),'00:00:00') AS 'LEAVE TIME'
FROM workers
LEFT JOIN (SELECT leave.ID_WORKER, SUM((datediff(leave.BEGIN_DATE, leave.END_DATE) + 1) * (time_to_sec(time(leave.END_DATE)) - time_to_sec(time(leave.BEGIN_DATE)))) AS ltime FROM leave
LEFT JOIN
(SELECT ID_WORKER, MIN(BEGIN_DATE) AS 'MIN_BEGIN_DATE', MAX(END_DATE) AS 'MAX_END_DATE' 
FROM order_status GROUP BY ID_WORKER) ordstat ON 
leave.ID_WORKER = ordstat.ID_WORKER 
WHERE leave.END_DATE <= MAX_END_DATE AND leave.BEGIN_DATE >= MIN_BEGIN_DATE GROUP BY leave.ID_WORKER) leaveAgg
ON leaveAgg.ID_WORKER = workers.ID_WORKER
LEFT JOIN (
SELECT order_status.ID_WORKER, orders.NUMBER_ORDER, orders.DESC_ORDER, 
SUM((TIME_TO_SEC(order_status.END_DATE) - TIME_TO_SEC(order_status.BEGIN_DATE))) AS stime 
FROM order_status INNER JOIN orders
ON orders.ID_ORDER = order_status.ID_ORDER
GROUP BY order_status.ID_WORKER) order_statusAgg
ON workers.ID_WORKER = order_statusAgg.ID_WORKER
WHERE order_statusAgg.NUMBER_ORDER LIKE 'TEST'
GROUP BY workers.ID_WORKER;

但恐怕这还不够，因为它只添加了例如:

22:30:21 + 8:00:00 = 30:30:21

我想得到那个结果:

22:30:21 + 2:00:16 = 24:30:37

注意!如果某人有 2 天或更多天的假期，我不能这样做:

                    '08:31:40'                                      
(-------------------------------------------------) * 16:00:00 
 '08:31:40' + '08:37:00' + '08:53:03' + '08:29:01'

我必须像我解释的那个例子一样每天分析。

我知道这是一个更难的问题，我找不到任何解决方法。有人可以请我更改或添加什么吗？预先感谢您提供任何帮助或建议。

最佳答案

您的 order_status 表中似乎有一些额外的数据，因为我得到了一些额外的行。但是过滤掉这些并运行一天我得到了结果。我在电子表格中仔细检查了结果，它似乎是正确的:

(08:31:40/34:30:44) * 08:00:00 = 01:58:36.3194

无论如何，这是查询，您正在寻找 window functions解决您的问题:

SELECT *,
SEC_TO_TIME((d.ORDER_TIME / d.DAILY_TOTAL) * 28800) as 'LEAVE TIME'
FROM
(
SELECT  os.*,
        o.NUMBER_ORDER,
        ((TIME_TO_SEC(os.END_DATE) - TIME_TO_SEC(os.BEGIN_DATE))) AS 'ORDER_TIME',
        (SUM(TIME_TO_SEC(os.END_DATE) - TIME_TO_SEC(os.BEGIN_DATE)) OVER (PARTITION BY CAST(os.END_DATE AS DATE) ORDER BY o.ID_ORDER ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) as 'DAILY_TOTAL'
FROM order_status os
    INNER JOIN orders o ON o.ID_ORDER = os.ID_ORDER
WHERE   (
        CAST(os.BEGIN_DATE AS DATE) = '2019-03-20' OR
        CAST(os.END_DATE AS DATE) = '2019-03-20'
        )
        AND NOT (
                    os.ID_STATUS = 32 AND
                    os.ID_ORDER = 19 AND
                    os.ID_WORKER = 26)
) d

关于mysql - 按比例正确汇总休假时间 - mysql，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/55443247/

mysql 汇总 39 2019 ORDER sql datetime sum

有关mysql - 按比例正确汇总休假时间 - mysql的更多相关文章

ruby-on-rails - 如何使用 instance_variable_set 正确设置实例变量？ - 2
我正在查看instance_variable_set的文档并看到给出的示例代码是这样做的:obj.instance_variable_set(:@instnc_var,"valuefortheinstancevariable")然后允许您在类的任何实例方法中以@instnc_var的形式访问该变量。我想知道为什么在@instnc_var之前需要一个冒号:。冒号有什么作用？最佳答案我的第一直觉是告诉你不要使用instance_variable_set除非你真的知道你用它做什么。它本质上是一种元编程工具或绕过实例变量可见性的黑客攻击
ruby-on-rails - Ruby 检查日期时间是否为 iso8601 并保存 - 2
我需要检查DateTime是否采用有效的ISO8601格式。喜欢:#iso8601?我检查了ruby是否有特定方法，但没有找到。目前我正在使用date.iso8601==date来检查这个。有什么好的方法吗？编辑解释我的环境，并改变问题的范围。因此，我的项目将使用jsapiFullCalendar，这就是我需要iso8601字符串格式的原因。我想知道更好或正确的方法是什么，以正确的格式将日期保存在数据库中，或者让ActiveRecord完成它们的工作并在我需要时间信息时对其进行操作。最佳答案我不太明白你的问题。我假设您想检查
ruby-on-rails - 将 Ruby 中的日期/时间格式化为 YYYY-MM-DD HH :MM:SS - 2
这个问题在这里已经有了答案:Railsformattingdate(4个答案)关闭4年前。我想格式化Time.Now函数以显示YYYY-MM-DDHH:MM:SS而不是:“2018-03-0909:47:19+0000”该函数需要放在时间中.现在功能。require‘roo’require‘roo-xls’require‘byebug’file_name=ARGV.first||“Template.xlsx”excel_file=Roo::Spreadsheet.open(“./#{file_name}“,extension::xlsx)xml=Nokogiri::XML::Build
ruby-on-rails - 正确的 Rails 2.1 做事方式 - 2
question的一些答案关于redirect_to让我想到了其他一些问题。基本上，我正在使用Rails2.1编写博客应用程序。我一直在尝试自己完成大部分工作(因为我对Rails有所了解)，但在需要时会引用Internet上的教程和引用资料。我设法让一个简单的博客正常运行，然后我尝试添加评论。靠我自己，我设法让它进入了可以从script/console添加评论的阶段，但我无法让表单正常工作。我遵循的其中一个教程建议在帖子Controller中创建一个“评论”操作，以添加评论。我的问题是:这是“标准”方式吗？我的另一个问题的答案之一似乎暗示应该有一个CommentsController参
ruby - 查找字符串中的内容类型(数字、日期、时间、字符串等) - 2
我正在尝试解析一个CSV文件并使用SQL命令自动为其创建一个表。CSV中的第一行给出了列标题。但我需要推断每个列的类型。Ruby中是否有任何函数可以找到每个字段中内容的类型。例如，CSV行:"12012","Test","1233.22","12:21:22","10/10/2009"应该产生像这样的类型['integer','string','float','time','date']谢谢! 最佳答案 require'time'defto_something(str)if(num=Integer(str)rescueFloat(s
ruby - 我可以将我的 README.textile 以正确的格式放入我的 RDoc 中吗？ - 2
我喜欢使用Textile或Markdown为我的项目编写自述文件，但是当我生成RDoc时，自述文件被解释为RDoc并且看起来非常糟糕。有没有办法让RDoc通过RedCloth或BlueCloth而不是它自己的格式化程序运行文件？它可以配置为自动检测文件后缀的格式吗？(例如README.textile通过RedCloth运行，但README.mdown通过BlueCloth运行) 最佳答案使用YARD直接代替RDoc将允许您包含Textile或Markdown文件，只要它们的文件后缀是合理的。我经常使用类似于以下Rake任务的东西:
ruby-on-rails - 使用 config.threadsafe 时从 lib/加载模块/类的正确方法是什么!选项？ - 2
我一直致力于让我们的Rails2.3.8应用程序在JRuby下正确运行。一切正常，直到我启用config.threadsafe!以实现JRuby提供的并发性。这导致lib/中的模块和类不再自动加载。使用config.threadsafe!启用:$rubyscript/runner-eproduction'pSim::Sim200Provisioner'/Users/amchale/.rvm/gems/jruby-1.5.1@web-services/gems/activesupport-2.3.8/lib/active_support/dependencies.rb:105:in`co
ruby - 调用其他方法的 TDD 方法的正确方法 - 2
我需要一些关于TDD概念的帮助。假设我有以下代码defexecute(command)casecommandwhen"c"create_new_characterwhen"i"display_inventoryendenddefcreate_new_character#dostufftocreatenewcharacterenddefdisplay_inventory#dostufftodisplayinventoryend现在我不确定要为什么编写单元测试。如果我为execute方法编写单元测试，那不是几乎涵盖了我对create_new_character和display_invent
使用canal同步MySQL数据到ES - 2
文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co
sql - 查询忽略时间戳日期的时间范围 - 2
我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。最佳答案您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

mysql - 按比例正确汇总休假时间 - mysql

有关mysql - 按比例正确汇总休假时间 - mysql的更多相关文章

随机推荐