需要 MySQL 查询总和和组建议-6ren

需要 MySQL 查询总和和组建议

转载作者：行者123 更新时间：2023-11-30 00:34:11

我尝试了一段时间来运行查询，但这比我想象的要困难。我有以下 3 个表:

Stores:
+--+-------+--------------+-------+---------+---------+----+---------------+---+------+
|ID|Company|Address       |PostCod|Latitude |Longitude|Stat|   Division    |Gro| When |
+--+-------+--------------+-------+---------+---------+----+---------------+---+------+
|17|Company|Site Address 3|WF1 5NT|53.666340|-1.487857|OPEN|Test Division 2|arl|2014-01-31 14:36:04
|18|Company|Site Address 3|WF1 5NT|53.666340|-1.487857|OPEN|Test Division 2|arl|2014-01-31 14:36:04
|19|Company|Site Address 3|WF1 5NT|53.666340|-1.487857|OPEN|Test Division 2|arl|2014-01-31 14:36:04
|20|Company|Site Address 3|WF1 5NT|53.666340|-1.487857|OPEN|Test Division 2|arl|2014-01-31 14:36:04
+--+-------+--------------+-------+---------+---------+----+---------------+---+------+

Jobs:
+--------+-------------------------------------------+-------+------------+------+--------+
| client | description                               | freq  | from       | till |job_id |
+--------+-------------------------------------------+-------+------------+------+--------+
|     17 | Weekly external and internal window clean |  7    | 2013-10-01 | NULL |    17 |
|     18 | Weekly external and internal window clean |  7    | 2013-10-01 | NULL |    18 |
|     19 | Weekly external and internal window clean |  7    | 2013-10-01 | NULL |    19 |
|     20 | Weekly external and internal window clean |  7    | 2013-10-01 | NULL |    20 |
|     17 | 4 weekly fascia and upper floor windows   | 28    | 2013-10-01 | NULL |   645 |
|     18 | 4 weekly fascia and upper floor windows   | 28    | 2013-10-01 | NULL |   646 |
|     19 | 4 weekly fascia and upper floor windows   | 28    | 2013-10-01 | NULL |   647 |
|     20 | 4 weekly fascia and upper floor windows   | 28    | 2013-10-01 | NULL |   648 |
+--------+-------------------------------------------+-------+------------+------+--------+


Job_hist
+------+-------------------+-----------------+---------+------+--------------+-----------------------------------------+-------------------------------+------+-----+-----------------------+------+
|Job_id|       last        |       user      | Company | LINK | Signedy_by   |       Job Description                   |          Address              | Accu |Days | Possition             |Uniqid|
+------+-------------------+-----------------+---------+------+--------------+-----------------------------------------+-------------------------------+------+-----+-----------------------+------+
|17    |2013-10-01 09:35:37|ARL_Operative_013|Santander| LINK |tony moore    |Weekly external window clean             | AN WELLING 14 BR (DA16 3PP)   |739.00|10   |Branch Manager         |132   |
|20    |2013-10-02 12:27:51|ARL_Operative_013|Santander| LINK |alex goodman  |Weekly external window clean             | AN HAROLD HILL 69 FR (RM3 8XA)|55.00 |6    |Store Assistant Manager|268   |
|19    |2013-10-03 09:14:19|ARL_Operative_013|Santander| LINK |darren pickett|Weekly external window clean             | AN WOOLWICH 41 PS (SE18 6JD)  |50.00 |5    |Other                  |332   |
|18    |2013-10-03 09:54:49|ARL_Operative_013|Santander| LINK |james lawrence|Weekly external window clean             | AN ELTHAM 73 EHS (SE9 1UW)    |49.00 |7    |Other                  |346   |
|17    |2013-10-08 09:05:16|ARL_Operative_013|Santander| LINK |tony moore    |Weekly external and internal window clean| AN WELLING 14 BR (DA16 3PP)   |67.00 |6    |Branch Manager         |697   |
+------+-------------------+-----------------+---------+------+--------------+-----------------------------------------+-------------------------------+------+-----+-----------------------+------+

我需要编写一个查询，根据除法进行一些性能计算。目标是:

计算给定时间范围内安排的清洁次数(简单版本)，获取开始日期和结束日期，计算天数并除以商店。freq
在此期间按时完成了多少计划清洁。我们从 job_hist 中获取这些数据(其中 job_hist.jod_id=jobs.job_id 和 job_hist.last 落在间隔内，并且 job_hist.days <= freq
按时完成清洁的百分比
有多少次清理工作延迟完成(与第 2 点中的逻辑相同，只是 job_hist> freq)
较晚完成的清洁百分比
错过了多少次(预定 - 按时完成 - 延迟完成)
错过的百分比

所有这些都应该按部门分组，因此结果应该如下所示:

+------------------------------+---------+------+-----------------+---------+--------------------+------+-----------------+
|division                      |scheduled|ontime|ontime_percentage|completed|completed_percentage|missed|missed_percentage
+------------------------------+---------+------+-----------------+---------+--------------------+------+-----------------+
|fsdfoihsdfljksdlgjdfsligsgfsfd|16282    |10404 |63.90%           |10825    |66.48%              |5457  |33.52%
|Test Division 2               |259      |129   |49.81%           |133      |51.35%              |126   |48.65%
|Test Division 3               |30       |15    |50.00%           |15       |50.00%              |15    |50.00%
+------------------------------+---------+------+-----------------+---------+--------------------+------+-----------------+

现在我不太擅长 SQL 查询，但我已经成功地组合了以下查询:

SELECT `stores`.`division`, 
SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) as `scheduled`, 
count(`name`)  as `ontime`, 
concat(round(( count(`name`)/SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) * 100 ),2),'%') AS ontime_percentage,
 count(`user`) as `completed`,
concat(round(( count(`user`)/SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) * 100 ),2),'%') AS completed_percentage,
(SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`)-count(`user`)) as `missed`,
concat(round(( (SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`)-count(`user`)) /SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) * 100 ),2),'%') AS missed_percentage
from `stores` left join `jobs` on `stores`.`id`=`jobs`.`client`
left join (select `user`,`job_id` from `job_hist` where  `job_hist`.`last`>='$from' and `job_hist`.`last`<='$till') as `myquery` on `myquery`.`job_id`=`jobs`.`job_id`
left join (select `name`,`job_hist`.`job_id` from `job_hist` left join `jobs` on `jobs`.`job_id`=`job_hist`.`job_id` where `job_hist`.`last`>='$from' and `job_hist`.`last`<='$till' and `job_hist`.`days`<=`jobs`.`freq`) as `myquery2` on `myquery2`.`job_id`=`jobs`.`job_id`
where `stores`.`owner`='$group'
group by `division`

$till、$from、$group 是一些 PHP 变量，$till 和 $from 是字符串格式的日期，$group 是字符串。

现在查询运行良好，但是(总是有一个但是)，Scheduled 字段正在汇总不同的值，我的意思是有时它使值加倍，有时它使值增加三倍。

我已阅读THIS但我仍然无法理解它。我或多或少明白我的一些结果翻了一番，但仍然不知道我需要改变什么。

在有人建议加入 stores.division 和 job_hist.division 之间的划分之前，这是不可行的，由于部门可以及时更改，并且它们会在 stores 中更新，但不会在 job_hist 中更新。job_hist 不应随时修改，因此仅包含历史数据。

更新

好的，我设法调整我的查询，到目前为止，我看起来得到了正确的数据，但在发布我自己的答案之前，我需要运行更多测试来确定。无论如何，这是我更新的查询:

SELECT `stores`.`division`, 
SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`)   as `scheduled`,
sum(`ontime`), 
concat(round(( sum(`ontime`)/SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) * 100 ),2),'%') AS ontime_percentage, 
sum(`completed`-`ontime`), 
concat(round(( sum(`completed`-`ontime`)/SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) * 100 ),2),'%') AS completed_percentage, 
(SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`)-sum(`completed`)) as `missed`, 
concat(round(( (SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`)-sum(`completed`)) /SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) * 100 ),2),'%') AS missed_percentage from `stores` 
left join `jobs` on `stores`.`id`=`jobs`.`client` 
left join (select count(`user`) as `completed`,`job_id` from `job_hist` where `job_hist`.`last`>='$from' and `job_hist`.`last`<='$till' group by `job_id`) as `myquery` on `myquery`.`job_id`=`jobs`.`job_id` 
left join (select count(`name`) as `ontime`,`job_hist`.`job_id` from `job_hist` left join `jobs` on `jobs`.`job_id`=`job_hist`.`job_id` where `job_hist`.`last`>='$from' and `job_hist`.`last`<='$till' and `job_hist`.`days`<=`jobs`.`freq` group by `job_id`) as `myquery2` on `myquery2`.`job_id`=`jobs`.`job_id` 
where `stores`.`owner`='$group' 
group by `stores`.`division`

我愿意接受更好的方式来运行此查询。

最佳答案

问题出在分组上，因此这里是正确返回数据的查询:

SELECT `stores`.`division`, 
SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`)   as `scheduled`,
sum(`ontime`), 
concat(round(( sum(`ontime`)/SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) * 100 ),2),'%') AS ontime_percentage, 
sum(`completed`)-sum(`ontime`), 
concat(round(( (sum(`completed`)-sum(`ontime`))/SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) * 100 ),2),'%') AS completed_percentage, 
(SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`)-sum(`completed`)) as `missed`, 
concat(round(( (SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`)-sum(`completed`)) /SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) * 100 ),2),'%') AS missed_percentage from `stores` 
left join `jobs` on `stores`.`id`=`jobs`.`client` 
left join (select count(`user`) as `completed`,`job_id` from `job_hist` where `job_hist`.`last`>='$from' and `job_hist`.`last`<='$till' group by `job_id`) as `myquery` on `myquery`.`job_id`=`jobs`.`job_id` 
left join (select count(`name`) as `ontime`,`job_hist`.`job_id` from `job_hist` left join `jobs` on `jobs`.`job_id`=`job_hist`.`job_id` where `job_hist`.`last`>='$from' and `job_hist`.`last`<='$till' and `job_hist`.`days`<=`jobs`.`freq` group by `job_id`) as `myquery2` on `myquery2`.`job_id`=`jobs`.`job_id` 
where `stores`.`owner`='$group' 
group by `stores`.`division`

如果有人有更好的解决方案，请发布。

关于需要 MySQL 查询总和和组建议，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/22302992/

文章推荐： PHP 循环行值

文章推荐： javascript - JWPlayer JS 插件添加到 flash 版本

文章推荐： javascript - 渲染多个 d3 图表而不相互覆盖

文章推荐： JavaPreparedStatement setString() 在查询中添加引号(mysql jdbc)

Mysql 查询 JOIN 查询
我有三张 table 。表 A 有选项名称(即颜色、尺寸)。表 B 有选项值名称(即蓝色、红色、黑色等)。表C通过将选项名称id和选项名称值id放在一起来建立关系。我的查询需要显示值和选项的名称，而
查询
在mysql中，如何计算一行中的非空单元格？我只想计算某些列之间的单元格，比如第 3-10 列之间的单元格。不是所有的列...同样，仅在该行中。最佳答案如果你想这样做，只能在 sql 中使用名称而
sql - 查询、 native 查询、命名查询和类型化查询之间的区别
关闭。这个问题需要多问focused 。目前不接受答案。想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
elasticsearch - 在Elasticsearch查询中没有为[查询]注册的[查询]
我正在为版本7.6进行Elasticsearch查询我的查询是这样的: { "query": { "bool": { "should": [ {
sql - 查询、 native 查询、命名查询和类型化查询之间的区别
关闭。这个问题需要多问focused 。目前不接受答案。想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
php - Mysql WHERE NOT EXISTS(查询)OR(查询)
是否可以编写一个查询来检查任一子查询(而不是一个子查询)是否正确？ SELECT * FROM employees e WHERE NOT EXISTS (
javascript - 查询。为表中的每一行发送 ajax 查询
我找到了很多关于我的问题的答案，但问题没有解决我有表格，有数据，例如: Data 1 Data 2 Data 3
salesforce - SOQL 查询 - 如何通过将字段设为小写并进行比较来编写 SOQL 查询？
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
salesforce - SOQL 查询 - 如何通过将字段设为小写并进行比较来编写 SOQL 查询？
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
Android SQLite 查询(我想解析一般的 SQL 查询)
我从 EditText 中获取了 String 值。以及提交查询的按钮。 String sql=editQuery.getText().toString();// SELECT * FROM empl
mysql 查询 - 为一个巨大的表优化现有的 MAX-MIN 查询
我有一个或多或少有效的查询(关于结果)，但处理大约需要 45 秒。这对于在 GUI 中呈现数据来说肯定太长了。所以我的需求是找到一个更快/更高效的查询(几毫秒左右会很好)我的数据表大约有 3000
SQL 查询 - 将 NULL 结果添加到 SELECT 查询
这是我第一次使用 Stack Overflow，所以我希望我以正确的方式提出这个问题。我有 2 个 SQL 查询，我正在尝试比较和识别缺失值，尽管我无法将 NULL 字段添加到第二个查询中以识别缺失
sql - 什么是动态 SQL 查询？何时需要使用动态 SQL 查询？
什么是动态 SQL 查询？何时需要使用动态 SQL 查询？我使用的是 SQL Server 2005。最佳答案这里有几篇文章: Introduction to Dynamic SQL Dynami
php - 在另一个 mysql 查询 while 循环中调用 mysql 查询
include "mysql.php"; $query= "SELECT ID,name,displayname,established,summary,searchlink,im
java - MySQL 查询 "select top 5"查询
我有一个查询要“转换”为 mysql。这是查询: select top 5 * from (select id, firstName, lastName, sum(fileSize) as To
c# - Entity Framework 查询 ToString 不会产生 SQL 查询
通过我的研究，我发现至少从 EF 4.1 开始，EF 查询上的 .ToString() 方法将返回要运行的 SQL。事实上，这对我来说非常有用，使用 Entity Framework 5 和 6。但
MySQL 查询(或 Doctrine 1.2 查询)- 从连接表和过滤器中获取最新项目
我在构造查询来执行以下操作时遇到问题: 按activity_type_id过滤联系人，仅显示最近事件具有所需activity_type_id或为NULL(无事件)的联系人表格结构如下: 一个联系人可
php - 如何在执行另一个 SQL 查询 x 分钟后执行一个 SQL 查询？
如何让我输入数据库的信息在输入数据 5 分钟后自行更新？假设我有一张 table : +--+--+-----+ |id|ip|count| +--+--+-----+ |
database - 如何在 N1QL 查询(Couchbase 查询)中使用 LENGTH() 字符串函数
我正在尝试搜索正好是 4 位数字的 ID，我知道我需要使用 LENGTH() 字符串函数，但找不到如何使用它的示例。我正在尝试以下(和其他变体)但它们不起作用。 SELECT max(car_id)
php - 将 SQL 查询 (+JOIN) 转换为 Symfony Propel 查询
我有一个在 mysql 上运行良好的 sql 查询(查询 + 连接): select sum(pa.price) from user u , purchase pu , pack pa where (

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

需要 MySQL 查询总和和组建议

更新