大家下午好,
我在 MySql 表中有两条或更多相同的数据记录。如果只有一个基于标志状态对的数据,我如何将相同的数据计算为一和零。下面是一个数据示例:
GLOBAL_ID FLAG_STATUS
VTSA-C-B002 IN
VTSA-C-B002 OUT
VTSA-C-B003 IN
VTSA-C-B031 IN
VTSA-C-B031 OUT
VTSA-C-B031 OUT
VTSA-C-B002 OUT
VTSA-C-B005 OUT
ABCD-D-B004 IN
ABCD-D-B001 IN
ABCD-D-B003 IN
ABCD-D-B001 OUT
ABCD-D-B003 OUT
ABCD-D-B005 IN
然后我想要计算的结果如下所示
Global ID Count
VTSA-C-B002 1 (Because have a Pair Flag Status ("IN" AND "OUT")
VTSA-C-B003 0 (Because Dont have a Pair Flag Status ("IN" AND "OUT")
VTSA-C-B031 1
VTSA-C-B005 0
ABCD-D-B004 0
ABCD-D-B001 1
ABCD-D-B003 1
ABCD-D-B005 0
所以最终的结果是这样总结计数的,
Global_ID ALias SUM of counting
VTSA 2
ABCD 2
我在下面尝试了这个查询,但结果正如我所寻找的
SELECT global_id,
mandays
FROM ( SELECT COUNT(DISTINCT b.global_id) AS mandays,
CASE WHEN b.global_id like "%ABCD%" THEN 'ABCD' ELSE 'VTSA' END AS global_id
FROM she_cii.tb_gate_log b
WHERE b.global_id REGEXP 'VTSA|ABCD'
AND date(b.received_date)="2024-02-03"
AND flag_status IN("IN","OUT")
group by SUBSTRING(b.global_id FROM POSITION('-' in b.global_id)+1 FOR 5)
) x
该查询的结果如下所示
global_id mandays
VTSA 36 (counting all distint global id record)
ABCD 45 (counting all distint global id record)
谢谢您的问候。
根据您给定的数据示例,可以通过使用轻松找到这些对
结果将是
现在,根据计数,您可以使用外部查询,例如
结果
参见示例
编辑
要加快给定条件的查询速度,请执行以下步骤
日期函数不使用索引(如果存在),替换
和
您正在寻找以或
global_id
开头的,替换VTSA
ABCD
和
最终查询将是
拥有
index flgst_glbid_recdt(flag_status ,global_id ,received_date )
将加快查询速度。小提琴