时间戳

首页 > 解决方案 > 来自 max() 的 MySQL 计数结果

mysql - 来自 max() 的 MySQL 计数结果

问题描述

我有一个 MySQL 表，用于跟踪进出该位置的库存扫描。像这样的示例数据：

bar_code_id,scan_type,scan_timestamp
038755,in,"2020-03-19 16:58:18"
038755,out,"2020-03-10 12:51:47"
001548,in,"2020-03-06 14:50:41"
012170,in,"2020-03-06 14:50:28"
020166,in,"2020-03-06 14:49:48"
022908,in,"2020-03-06 14:49:07"
012169,out,"2020-03-02 11:48:43"
020182,out,"2020-03-02 11:48:24"
047270,in,"2020-01-29 16:54:18"
020166,out,"2020-01-20 11:43:44"
012170,out,"2020-01-20 11:43:32"
022908,out,"2020-01-20 11:43:23"
001548,out,"2020-01-20 11:43:16"
016852,in,"2020-01-20 09:42:07"
020182,in,"2020-01-20 09:42:02"
012172,out,"2020-01-14 12:56:00"
012172,in,"2020-01-14 11:06:22"
001548,in,"2020-01-14 10:46:47"
010606,in,"2020-01-14 10:46:46"
012169,in,"2020-01-14 10:46:45"
020166,in,"2020-01-14 10:46:45"
010054,in,"2020-01-13 14:59:50"
012167,in,"2020-01-13 14:58:33"
020175,out,"2020-01-09 12:16:16"
023260,in,"2020-01-07 12:01:12"
038755,in,"2020-01-06 09:15:23"

我正在尝试创建一个查询，该查询将显示建筑物内的当前号码。我可以使用以下方法找到每个资产的最新扫描：

select bar_code_id, max(scan_timestamp)
from scans
group by bar_code_id
order by bar_code_id

给出以下结果：

bar_code_id,timestamp
001548,"2020-03-06 14:50:41"
010054,"2020-01-13 14:59:50"
010606,"2020-01-14 10:46:46"
012167,"2020-01-13 14:58:33"
012169,"2020-03-02 11:48:43"
012170,"2020-03-06 14:50:28"
012172,"2020-01-14 12:56:00"
016852,"2020-01-20 09:42:07"
020166,"2020-03-06 14:49:48"
020175,"2020-01-09 12:16:16"
020182,"2020-03-02 11:48:24"
022908,"2020-03-06 14:49:07"
023260,"2020-01-07 12:01:12"
038755,"2020-03-19 16:58:18"
047270,"2020-01-29 16:54:18"

但也无法弄清楚如何显示扫描类型。理想情况下，我希望能够计算最后一次扫描“进入”的项目数量以及项目总数。

任何指针？

编辑

我试图只查看每个 bar_code 的最新扫描，然后计算 scan_type 为“in”的数字

所以对每个 bar_code 的最新扫描是：

bar_code_id,scan_type,scan_timestamp
001548,in,2020-03-06 14:50:41
010054,in,2020-01-13 14:59:50
010606,in,2020-01-14 10:46:46
012167,in,2020-01-13 14:58:33
012169,out,2020-03-02 11:48:43
012170,in,2020-03-06 14:50:28
012172,out,2020-01-14 12:56:00
016852,in,2020-01-20 09:42:07
020166,in,2020-03-06 14:49:48
020175,out,2020-01-09 12:16:16
020182,out,2020-03-02 11:48:24
022908,in,2020-03-06 14:49:07
023260,in,2020-01-07 12:01:12
038755,in,2020-03-19 16:58:18
047270,in,2020-01-29 16:54:18

然后输入'in'的那些是：

bar_code_id,scan_type,scan_timestamp
001548,in,2020-03-06 14:50:41
010054,in,2020-01-13 14:59:50
010606,in,2020-01-14 10:46:46
012167,in,2020-01-13 14:58:33
012170,in,2020-03-06 14:50:28
016852,in,2020-01-20 09:42:07
020166,in,2020-03-06 14:49:48
022908,in,2020-03-06 14:49:07
023260,in,2020-01-07 12:01:12
038755,in,2020-03-19 16:58:18
047270,in,2020-01-29 16:54:18

给出11的答案。

我使用 max(scan_timestamp) 的 sql 查询不包括 scan_type，因此无法计算在内。如果我尝试包含这个，那么 max 函数显示的不仅仅是最新的扫描

select bar_code_id, max(scan_timestamp) as timestamp, scan_type
from scans
group by bar_code_id, scan_type

bar_code_id,timestamp,scan_type
038755,"2020-03-19 16:58:18",in
020166,"2020-01-20 11:43:44",out
012170,"2020-01-20 11:43:32",out
022908,"2020-01-20 11:43:23",out
001548,"2020-01-20 11:43:16",out
016852,"2020-01-20 09:42:07",in
020182,"2020-01-20 09:42:02",in
012172,"2020-01-14 12:56:00",out
012172,"2020-01-14 11:06:22",in
001548,"2020-03-06 14:50:41",in
010606,"2020-01-14 10:46:46",in
038755,"2020-03-10 12:51:47",out
012169,"2020-01-14 10:46:45",in
020166,"2020-03-06 14:49:48",in
010054,"2020-01-13 14:59:50",in
012167,"2020-01-13 14:58:33",in
020175,"2020-01-09 12:16:16",out
023260,"2020-01-07 12:01:12",in
012170,"2020-03-06 14:50:28",in
022908,"2020-03-06 14:49:07",in
012169,"2020-03-02 11:48:43",out
020182,"2020-03-02 11:48:24",out
047270,"2020-01-29 16:54:18",in

标签： mysql

解决方案

我不知道这是否真的是你想要的，但我有这个要求：

SELECT bar_code_id, MAX(scan_timestamp) as timetamps, scan_type, COUNT(scan_type) as number_of_items, (SELECT COUNT(id) from scans) as total
FROM scans
WHERE scan_type = 'in'
GROUP BY bar_code_id
ORDER BY bar_code_id

这是我测试此请求的链接：http ://sqlfiddle.com/#!9/0da168/19

如果您对我的回答有任何疑问，我随时待命。

推荐阅读