- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有点难打败我。我们有一个生产各种产品的仓库,我们使用以下查询发送每日生产报告,但我希望每小时对其进行排序:
(我将查询缩减为只有少数产品,因为它很重要:
SET
@DayStart = '2013-10-24 07:00:00',
@DayEnd = '2013-10-24 16:00:00';
SELECT
(SELECT COUNT(*) FROM t_container
WHERE created_timestamp BETWEEN @DayStart AND @DayEnd
AND container_type_id = '3'
) AS Product1,
(SELECT CONCAT (
(SELECT COUNT(*) FROM t_container
WHERE created_timestamp BETWEEN @DayStart AND @DayEnd
AND container_type_id = '10'
AND status_id = '4'
) , ' / ' ,
(SELECT COUNT(*) FROM t_container
WHERE created_timestamp BETWEEN @DayStart AND @DayEnd
AND container_type_id = '10'
AND status_id = '6'
))
) AS 'Product 1 EOL',
(SELECT COUNT(*) FROM t_container
WHERE created_timestamp BETWEEN @DayStart AND @DayEnd
AND container_type_id = '2'
) AS 'Product 2',
(SELECT COUNT(*) FROM t_container
WHERE created_timestamp BETWEEN @DayStart AND @DayEnd
AND container_type_id = '6'
) AS 'Product 3',
(SELECT CONCAT (
(SELECT COUNT(*) FROM t_container
WHERE created_timestamp BETWEEN @DayStart AND @DayEnd
AND container_type_id = '9'
AND status_id = '4'
) , ' / ' ,
(SELECT COUNT(*) FROM t_container
WHERE created_timestamp BETWEEN @DayStart AND @DayEnd
AND container_type_id = '9'
AND status_id = '6'
))
) AS 'Product 3 EOL',
(SELECT COUNT(*) FROM t_container
WHERE created_timestamp BETWEEN @DayStart AND @DayEnd
AND container_type_id = '4'
) AS 'Product 4',
(SELECT CONCAT (
(SELECT COUNT(*) FROM t_container
WHERE created_timestamp BETWEEN @DayStart AND @DayEnd
AND container_type_id = '8'
AND status_id = '4'
) , ' / ' ,
(SELECT COUNT(*) FROM t_container
WHERE created_timestamp BETWEEN @DayStart AND @DayEnd
AND container_type_id = '8'
AND status_id = '6'
))
) AS 'Product 4 EOL'
我想要实现的是运行上述报告,但输出以 1 小时为增量。
如有任何帮助,我们将不胜感激。
请看下面的“预期输出示例”
时间 |产品 1 |产品 1 停产 |产品 2 |产品 3
07:00 | 12345 | 1/1 | 1568789 | 1/1
08:00 | 12345 | 1/1 | 1568789 | 1/1
09:00 | 12345 | 1/1 | 1568789 | 1/1
最佳答案
你有一行包含很多子选择
我很想重新编码这些子选择,然后将它们连接成一行(或者只返回多行并在显示代码中进行格式化)。
然而,要将这些子选择之一拆分为几个小时,您可以使用类似这样的东西(未测试):-
SELECT StartHour, COUNT(*)
FROM
(
SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour
FROM
(
SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) Sub1
) Sub2
LEFT OUTER JOIN t_container
ON created_timestamp BETWEEN Sub2.StartHour AND DATE_ADD(Sub2.StartHour, INTERVAL 3599 SECOND)
WHERE created_timestamp BETWEEN @DayStart AND @DayEnd
AND container_type_id = '3'
GROUP BY StartHour
这会通过添加到开始日期来得出一系列小时时间段,然后使用它对您的数据进行左连接(添加 3599 秒以获得小时时间段的结束),然后进行分组计数,在开始时间进行 GROUP BY。
编辑
未测试,但这是一种方法:-
SELECT StartHour,
EndHour,
COUNT(DISTINCT a.id) AS Product1,
COUNT(DISTINCT b.id) AS 'Product 1 EOL 1',
COUNT(DISTINCT c.id) AS 'Product 1 EOL 2',
COUNT(DISTINCT d.id) AS 'Product 2',
COUNT(DISTINCT e.id) AS 'Product 3',
COUNT(DISTINCT f.id) AS 'Product 3 EOL 1',
COUNT(DISTINCT g.id) AS 'Product 3 EOL 2',
COUNT(DISTINCT h.id) AS 'Product 4',
COUNT(DISTINCT i.id) AS 'Product 4 EOL 1',
COUNT(DISTINCT j.id) AS 'Product 4 EOL 2'
FROM
(
SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
FROM
(
SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) Sub1
) Sub2
LEFT OUTER JOIN t_container a
ON a.created_timestamp BETWEEN Sub2.StartHour AND Sub2.EndHour
AND a.container_type_id = '3'
LEFT OUTER JOIN t_container b
ON b.created_timestamp BETWEEN Sub2.StartHour AND Sub2.EndHour
AND b.container_type_id = '10'
AND b.status_id = '4'
LEFT OUTER JOIN t_container c
ON c.created_timestamp BETWEEN Sub2.StartHour AND Sub2.EndHour
AND c.container_type_id = '10'
AND c.status_id = '6'
LEFT OUTER JOIN t_container d
ON d.created_timestamp BETWEEN Sub2.StartHour AND Sub2.EndHour
AND d.container_type_id = '2'
LEFT OUTER JOIN t_container e
ON e.created_timestamp BETWEEN Sub2.StartHour AND Sub2.EndHour
AND e.container_type_id = '6'
LEFT OUTER JOIN t_container f
ON f.created_timestamp BETWEEN Sub2.StartHour AND Sub2.EndHour
AND f.container_type_id = '9'
AND f.status_id = '4'
LEFT OUTER JOIN t_container g
ON g.created_timestamp BETWEEN Sub2.StartHour AND Sub2.EndHour
AND g.container_type_id = '9'
AND g.status_id = '6'
LEFT OUTER JOIN t_container h
ON h.created_timestamp BETWEEN Sub2.StartHour AND Sub2.EndHour
AND h.container_type_id = '4'
LEFT OUTER JOIN t_container i
ON i.created_timestamp BETWEEN Sub2.StartHour AND Sub2.EndHour
AND i.container_type_id = '8'
AND i.status_id = '4'
LEFT OUTER JOIN t_container j
ON j.created_timestamp BETWEEN Sub2.StartHour AND Sub2.EndHour
AND j.container_type_id = '8'
AND j.status_id = '6'
GROUP BY StartHour, EndHour
这是执行 1 个子选择以获取所有小时时间段,然后重新连接表以获取计数。然而,使用这么多 COUNT(DISTINCT )) 类型的语句可能不会执行得那么好。
可能可以进行连接以获取每个子选择中的小时范围,然后再连接回去,这可能会更好。
请注意,我已经为以前有 2 个值的列带回了 2 列,这些列被/分隔。
可能稍微好一点(但也可能稍微差一点):-
SELECT Product1.rec_cnt AS Product1,
CONCAT(Product1EOL1.rec_cnt, ' / ', Product1EOL2.rec_cnt) AS 'Product 1 EOL',
Product2.rec_cnt AS 'Product 2',
Product3.rec_cnt AS 'Product 3',
CONCAT(Product3EOL1.rec_cnt, ' / ', Product3EOL2.rec_cnt) AS 'Product 3 EOL',
Product4.rec_cnt AS 'Product 4',
CONCAT(Product4EOL1.rec_cnt, ' / ', Product4EOL2.rec_cnt) AS 'Product 4 EOL'
FROM
(
SELECT StartHour, EndHour, COUNT(t_container.id) AS rec_cnt
FROM
(
SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
FROM
(
SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) Sub1
) Sub2
LEFT OUTER JOIN t_container
ON created_timestamp BETWEEN StartHour AND EndHour
AND container_type_id = '3'
GROUP BY StartHour, EndHour
) AS Product1
INNER JOIN
(
SELECT StartHour, EndHour, COUNT(t_container.id) AS rec_cnt
FROM
(
SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
FROM
(
SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) Sub1
) Sub2
LEFT OUTER JOIN t_container
ON created_timestamp BETWEEN StartHour AND EndHour
AND container_type_id = '10'
AND status_id = '4'
GROUP BY StartHour, EndHour
) AS Product1EOL1
ON Product1.StartHour = Product1EOL1.StartHour AND Product1.EndHour = Product1EOL1.EndHour
INNER JOIN
(
SELECT StartHour, EndHour, COUNT(t_container.id) AS rec_cnt
FROM
(
SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
FROM
(
SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) Sub1
) Sub2
LEFT OUTER JOIN t_container
ON created_timestamp BETWEEN StartHour AND EndHour
AND container_type_id = '10'
AND status_id = '6'
GROUP BY StartHour, EndHour
) AS Product1EOL2
ON Product1.StartHour = Product1EOL2.StartHour AND Product1.EndHour = Product1EOL2.EndHour
INNER JOIN
(
SELECT StartHour, EndHour, COUNT(t_container.id) AS rec_cnt
FROM
(
SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
FROM
(
SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) Sub1
) Sub2
LEFT OUTER JOIN t_container
ON created_timestamp BETWEEN StartHour AND EndHour
AND container_type_id = '2'
GROUP BY StartHour, EndHour
) AS Product2
ON Product1.StartHour = Product2.StartHour AND Product1.EndHour = Product2.EndHour
INNER JOIN
(
SELECT StartHour, EndHour, COUNT(t_container.id) AS rec_cnt
FROM
(
SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
FROM
(
SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) Sub1
) Sub2
LEFT OUTER JOIN t_container
ON created_timestamp BETWEEN StartHour AND EndHour
AND container_type_id = '6'
GROUP BY StartHour, EndHour
) AS Product3
ON Product1.StartHour = Product3.StartHour AND Product1.EndHour = Product3.EndHour
INNER JOIN
(
SELECT StartHour, EndHour, COUNT(t_container.id) AS rec_cnt
FROM
(
SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
FROM
(
SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) Sub1
) Sub2
LEFT OUTER JOIN t_container
ON created_timestamp BETWEEN StartHour AND EndHour
AND container_type_id = '9'
AND status_id = '4'
GROUP BY StartHour, EndHour
) AS Product3EOL1
ON Product1.StartHour = Product3EOL1.StartHour AND Product1.EndHour = Product3EOL1.EndHour
INNER JOIN
(
SELECT StartHour, EndHour, COUNT(t_container.id) AS rec_cnt
FROM
(
SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
FROM
(
SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) Sub1
) Sub2
LEFT OUTER JOIN t_container
ON created_timestamp BETWEEN StartHour AND EndHour
AND container_type_id = '9'
AND status_id = '6'
GROUP BY StartHour, EndHour
) AS Product3EOL2
ON Product1.StartHour = Product3EOL2.StartHour AND Product1.EndHour = Product3EOL2.EndHour
INNER JOIN
(
SELECT StartHour, EndHour, COUNT(t_container.id) AS rec_cnt
FROM
(
SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
FROM
(
SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) Sub1
) Sub2
LEFT OUTER JOIN t_container
ON created_timestamp BETWEEN StartHour AND EndHour
AND container_type_id = '4'
GROUP BY StartHour, EndHour
) AS Product4
ON Product4.StartHour = Product3.StartHour AND Product4.EndHour = Product3.EndHour
INNER JOIN
(
SELECT StartHour, EndHour, COUNT(t_container.id) AS rec_cnt
FROM
(
SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
FROM
(
SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) Sub1
) Sub2
LEFT OUTER JOIN t_container
ON created_timestamp BETWEEN StartHour AND EndHour
AND container_type_id = '8'
AND status_id = '4'
GROUP BY StartHour, EndHour
) AS Product4EOL1
ON Product1.StartHour = Product4EOL1.StartHour AND Product1.EndHour = Product4EOL1.EndHour
INNER JOIN
(
SELECT StartHour, EndHour, COUNT(t_container.id) AS rec_cnt
FROM
(
SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
FROM
(
SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) Sub1
) Sub2
LEFT OUTER JOIN t_container
ON created_timestamp BETWEEN StartHour AND EndHour
AND container_type_id = '8'
AND status_id = '6'
GROUP BY StartHour, EndHour
) AS Product4EOL2
ON Product1.StartHour = Product4EOL2.StartHour AND Product1.EndHour = Product4EOL2.EndHour
关于mysql - SQL查询每小时报告?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19559181/
我有三张 table 。表 A 有选项名称(即颜色、尺寸)。表 B 有选项值名称(即蓝色、红色、黑色等)。表C通过将选项名称id和选项名称值id放在一起来建立关系。 我的查询需要显示值和选项的名称,而
在mysql中,如何计算一行中的非空单元格?我只想计算某些列之间的单元格,比如第 3-10 列之间的单元格。不是所有的列...同样,仅在该行中。 最佳答案 如果你想这样做,只能在 sql 中使用名称而
关闭。这个问题需要多问focused 。目前不接受答案。 想要改进此问题吗?更新问题,使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
我正在为版本7.6进行Elasticsearch查询 我的查询是这样的: { "query": { "bool": { "should": [ {
关闭。这个问题需要多问focused 。目前不接受答案。 想要改进此问题吗?更新问题,使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
是否可以编写一个查询来检查任一子查询(而不是一个子查询)是否正确? SELECT * FROM employees e WHERE NOT EXISTS (
我找到了很多关于我的问题的答案,但问题没有解决 我有表格,有数据,例如: Data 1 Data 2 Data 3
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
我从 EditText 中获取了 String 值。以及提交查询的按钮。 String sql=editQuery.getText().toString();// SELECT * FROM empl
我有一个或多或少有效的查询(关于结果),但处理大约需要 45 秒。这对于在 GUI 中呈现数据来说肯定太长了。 所以我的需求是找到一个更快/更高效的查询(几毫秒左右会很好)我的数据表大约有 3000
这是我第一次使用 Stack Overflow,所以我希望我以正确的方式提出这个问题。 我有 2 个 SQL 查询,我正在尝试比较和识别缺失值,尽管我无法将 NULL 字段添加到第二个查询中以识别缺失
什么是动态 SQL 查询?何时需要使用动态 SQL 查询?我使用的是 SQL Server 2005。 最佳答案 这里有几篇文章: Introduction to Dynamic SQL Dynami
include "mysql.php"; $query= "SELECT ID,name,displayname,established,summary,searchlink,im
我有一个查询要“转换”为 mysql。这是查询: select top 5 * from (select id, firstName, lastName, sum(fileSize) as To
通过我的研究,我发现至少从 EF 4.1 开始,EF 查询上的 .ToString() 方法将返回要运行的 SQL。事实上,这对我来说非常有用,使用 Entity Framework 5 和 6。 但
我在构造查询来执行以下操作时遇到问题: 按activity_type_id过滤联系人,仅显示最近事件具有所需activity_type_id或为NULL(无事件)的联系人 表格结构如下: 一个联系人可
如何让我输入数据库的信息在输入数据 5 分钟后自行更新? 假设我有一张 table : +--+--+-----+ |id|ip|count| +--+--+-----+ |
我正在尝试搜索正好是 4 位数字的 ID,我知道我需要使用 LENGTH() 字符串函数,但找不到如何使用它的示例。我正在尝试以下(和其他变体)但它们不起作用。 SELECT max(car_id)
我有一个在 mysql 上运行良好的 sql 查询(查询 + 连接): select sum(pa.price) from user u , purchase pu , pack pa where (
我是一名优秀的程序员,十分优秀!