A-A+
mysql语句统计夜间各审批人的工单数量
【注意：此文章为博主原创文章！转载需注意，请带原文链接，至少也要是txt格式！】
###mysql语句统计夜间各审批人的工单数量
SELECT
CASE
WHEN audit_users LIKE '%1021%' THEN 'Jeng (1421)'
WHEN audit_users LIKE '%1077%' THEN 'Kyng (1077)'
WHEN audit_users LIKE '%1071%' THEN 'Ch (1001)'
WHEN audit_users LIKE '%1065%' THEN 'Ton (1065)'
WHEN audit_users LIKE '%1015%' THEN 'Leiu (1915)'
WHEN audit_users LIKE '%1041%' THEN 'Big (1041)'
WHEN audit_users LIKE '%1041%' THEN 'Be(1006)'
WHEN audit_users LIKE '%1008%' THEN 'Siang (7808)'
WHEN audit_users LIKE '%1056%' THEN 'Yuu (1056)'
WHEN audit_users LIKE '%1007%' THEN 'Jaou (1507)'
WHEN audit_users LIKE '%1005%' THEN 'Pu(1005)'
WHEN audit_users LIKE '%1025%' THEN 'Ja (1005)'
WHEN audit_users LIKE '%1071%' THEN 'Jaang (4571)'
WHEN audit_users LIKE '%1023%' THEN 'YuXie (4623)'
WHEN audit_users LIKE '%1046%' THEN 'Kh (1006)'
WHEN audit_users LIKE '%1027%' THEN 'Ya (1007)'
WHEN audit_users LIKE '%1017%' THEN 'Sti (1017)'
WHEN audit_users LIKE '%1025%' THEN 'Ze (1005)'
WHEN audit_users LIKE '%1035%' THEN 'Ka(1028)'
ELSE '未知审批人'
END as 审批人,
COUNT(DISTINCT workflow_id) as 夜间审批工单数量, workflow_id
FROM
t_approval_node
WHERE
(
JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库权限续租申请%' OR
JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库查询权限申请%' OR
JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库数据导出%'
)
AND title LIKE '%安全%'
AND create_time >= '2025-07-01'
AND create_time <= '2025-07-15' AND audit_status = 2 #1-待审;2-通过;3-驳回;4-撤销 AND ( HOUR(create_time) >= 23 OR HOUR(create_time) < 5
)
GROUP BY
CASE
WHEN audit_users LIKE '%1021%' THEN 'Jeng (1421)'
WHEN audit_users LIKE '%1077%' THEN 'Kyng (1077)'
WHEN audit_users LIKE '%1071%' THEN 'Ch (1001)'
WHEN audit_users LIKE '%1065%' THEN 'Ton (1065)'
WHEN audit_users LIKE '%1015%' THEN 'Leiu (1915)'
WHEN audit_users LIKE '%1041%' THEN 'Big (1041)'
WHEN audit_users LIKE '%1041%' THEN 'Be(1006)'
WHEN audit_users LIKE '%1008%' THEN 'Siang (7808)'
WHEN audit_users LIKE '%1056%' THEN 'Yuu (1056)'
WHEN audit_users LIKE '%1007%' THEN 'Jaou (1507)'
WHEN audit_users LIKE '%1005%' THEN 'Pu(1005)'
WHEN audit_users LIKE '%1025%' THEN 'Ja (1005)'
WHEN audit_users LIKE '%1071%' THEN 'Jaang (4571)'
WHEN audit_users LIKE '%1023%' THEN 'YuXie (4623)'
WHEN audit_users LIKE '%1046%' THEN 'Kh (1006)'
WHEN audit_users LIKE '%1027%' THEN 'Ya (1007)'
WHEN audit_users LIKE '%1017%' THEN 'Sti (1017)'
WHEN audit_users LIKE '%1025%' THEN 'Ze (1005)'
WHEN audit_users LIKE '%1035%' THEN 'Ka(1028)'
ELSE '未知审批人'
END
ORDER BY
夜间审批工单数量 DESC;
###查询每个流程工单量总计
SELECT
CASE
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库权限续租申请%' THEN '数据库权限续租申请'
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库查询权限申请%' THEN '数据库查询权限申请'
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库数据导出%' THEN '数据库数据导出'
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%齐治堡垒机%' THEN '齐治堡垒机'
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.name') in 'etyer流程' THEN 'etyer流程'
END as 流程类型,
COUNT(DISTINCT workflow_id) as 工单数量
FROM
t_approval_node
WHERE
(
JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库权限续租申请%' OR
JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库查询权限申请%' OR
JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库数据导出%' OR
JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%齐治堡垒机%' OR
JSON_EXTRACT(attr_json, '$.workflow_info.name') in 'etyer流程'
)
AND title LIKE '%安全%'
AND create_time >= '2025-07-01'
AND create_time <= '2025-07-15'
AND audit_status = 2 #1-待审;2-通过;3-驳回;4-撤销
###这里注意屏蔽 %权限%申请
#AND (JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库%权限%申请%')
GROUP BY
CASE
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库权限续租申请%' THEN '数据库权限续租申请'
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库查询权限申请%' THEN '数据库查询权限申请'
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库数据导出%' THEN '数据库数据导出'
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%齐治堡垒机%' THEN '齐治堡垒机'
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.name') in 'etyer流程' THEN 'etyer流程'
END
ORDER BY
工单数量 DESC;
##每个人每个流程的数量统计
SELECT
CASE
WHEN audit_users LIKE '%101%' THEN 'Jeng (1421)'
WHEN audit_users LIKE '%107%' THEN 'Kyng (1077)'
WHEN audit_users LIKE '%101%' THEN 'Ch (1001)'
WHEN audit_users LIKE '%105%' THEN 'Ton (1065)'
WHEN audit_users LIKE '%105%' THEN 'Leiu (1915)'
WHEN audit_users LIKE '%101%' THEN 'Big (1041)'
WHEN audit_users LIKE '%101%' THEN 'Be(1006)'
WHEN audit_users LIKE '%108%' THEN 'Siang (7808)'
WHEN audit_users LIKE '%106%' THEN 'Yuu (1056)'
WHEN audit_users LIKE '%107%' THEN 'Jaou (1507)'
WHEN audit_users LIKE '%105%' THEN 'Pu(1005)'
WHEN audit_users LIKE '%105%' THEN 'Ja (1005)'
WHEN audit_users LIKE '%101%' THEN 'Jaang (4571)'
WHEN audit_users LIKE '%103%' THEN 'YuXie (4623)'
WHEN audit_users LIKE '%106%' THEN 'Kh (1006)'
WHEN audit_users LIKE '%107%' THEN 'Ya (1007)'
WHEN audit_users LIKE '%107%' THEN 'Sti (1017)'
WHEN audit_users LIKE '%105%' THEN 'Ze (1005)'
WHEN audit_users LIKE '%105%' THEN 'Ka(1028)'
ELSE '未知审批人'
END as 审批人,
CASE
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库权限续租申请%' THEN '数据库权限续租申请'
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库查询权限申请%' THEN '数据库查询权限申请'
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库数据导出%' THEN '数据库数据导出'
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%齐治堡垒机%' THEN '齐治堡垒机'
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.name') IN 'etyer流程' THEN 'etyer流程'
END as 流程类型,
COUNT(DISTINCT workflow_id) as 工单数量
FROM
t_approval_node
WHERE
(
JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库权限续租申请%' OR
JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库查询权限申请%' OR
JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库数据导出%' OR
JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%齐治堡垒机%' OR
JSON_EXTRACT(attr_json, '$.workflow_info.name') in 'etyer流程'
)
AND title LIKE '%安全%'
AND create_time >= '2025-07-01'
AND create_time <= '2025-07-15'
AND audit_status = 2 #1-待审;2-通过;3-驳回;4-撤销
GROUP BY
CASE
WHEN audit_users LIKE '%101%' THEN 'Jeng (1421)'
WHEN audit_users LIKE '%107%' THEN 'Kyng (1077)'
WHEN audit_users LIKE '%101%' THEN 'Ch (1001)'
WHEN audit_users LIKE '%105%' THEN 'Ton (1065)'
WHEN audit_users LIKE '%105%' THEN 'Leiu (1915)'
WHEN audit_users LIKE '%101%' THEN 'Big (1041)'
WHEN audit_users LIKE '%101%' THEN 'Be(1006)'
WHEN audit_users LIKE '%108%' THEN 'Siang (7808)'
WHEN audit_users LIKE '%106%' THEN 'Yuu (1056)'
WHEN audit_users LIKE '%107%' THEN 'Jaou (1507)'
WHEN audit_users LIKE '%105%' THEN 'Pu(1005)'
WHEN audit_users LIKE '%105%' THEN 'Ja (1005)'
WHEN audit_users LIKE '%101%' THEN 'Jaang (4571)'
WHEN audit_users LIKE '%103%' THEN 'YuXie (4623)'
WHEN audit_users LIKE '%106%' THEN 'Kh (1006)'
WHEN audit_users LIKE '%107%' THEN 'Ya (1007)'
WHEN audit_users LIKE '%107%' THEN 'Sti (1017)'
WHEN audit_users LIKE '%105%' THEN 'Ze (1005)'
WHEN audit_users LIKE '%105%' THEN 'Ka(1028)'
ELSE '未知审批人'
END,
CASE
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库权限续租申请%' THEN '数据库权限续租申请'
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库查询权限申请%' THEN '数据库查询权限申请'
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%数据库数据导出%' THEN '数据库数据导出'
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.instance_name') LIKE '%齐治堡垒机%' THEN '齐治堡垒机'
WHEN JSON_EXTRACT(attr_json, '$.workflow_info.name') IN 'etyer流程' THEN 'etyer流程'
END
HAVING
审批人 IS NOT NULL
ORDER BY
审批人, 流程类型;
#####每个人关于所有流程的单量之和统计"完整正确"
SELECT
CASE
WHEN t2.audit_user LIKE '%121%' THEN 'JWang (1421)'
WHEN t2.audit_user LIKE '%177%' THEN 'Khang (1077)'
WHEN t2.audit_user LIKE '%171%' THEN 'Ciu (1001)'
WHEN t2.audit_user LIKE '%165%' THEN 'Tuan (1065)'
WHEN t2.audit_user LIKE '%115%' THEN 'L Liu (1915)'
WHEN t2.audit_user LIKE '%141%' THEN 'Bang (1041)'
WHEN t2.audit_user LIKE '%141%' THEN 'Bn (1006)'
WHEN t2.audit_user LIKE '%108%' THEN 'SJiang (7808)'
WHEN t2.audit_user LIKE '%156%' THEN 'YLiu (1056)'
WHEN t2.audit_user LIKE '%107%' THEN 'JZhou (1507)'
WHEN t2.audit_user LIKE '%105%' THEN 'Pe (1005)'
WHEN t2.audit_user LIKE '%125%' THEN 'Jou (1005)'
WHEN t2.audit_user LIKE '%171%' THEN 'JJiang (4571)'
WHEN t2.audit_user LIKE '%123%' THEN 'Yg Xie (4623)'
WHEN t2.audit_user LIKE '%146%' THEN 'Kiu (1006)'
WHEN t2.audit_user LIKE '%127%' THEN 'Yin (1007)'
WHEN t2.audit_user LIKE '%117%' THEN 'S Li (1017)'
WHEN t2.audit_user LIKE '%125%' THEN 'Zan (1005)'
WHEN t2.audit_user LIKE '%135%' THEN 'Kv (1028)'
END as 审批人,
COUNT(DISTINCT t1.workflow_id) as 审批工单总数
FROM
t_approval_node t1
INNER JOIN t_approval_record t2 ON t1.approval_num = t2.approval_num
WHERE
t2.audit_status = 2 #状态：1-待审;2-通过;3-驳回;4–撤销;1-转交
AND t1.title LIKE '%安全%'
AND t2.audit_user IN ('121','177','171','165','115','141','141','108','156','107','105','125','171','123','146','127','117','125','135') ##此处是所有BP工号，与上方相对应
AND t1.create_time >= '2025-07-14 00:00:00'
AND t1.create_time <= '2025-07-18 23:59:59'
AND (
JSON_EXTRACT(t1.attr_json, '$.workflow_info.instance_name') LIKE '%数据库权限续租申请%' OR
JSON_EXTRACT(t1.attr_json, '$.workflow_info.instance_name') LIKE '%数据库查询权限申请%' OR
JSON_EXTRACT(t1.attr_json, '$.workflow_info.instance_name') LIKE '%数据库数据导出%' OR
JSON_EXTRACT(t1.attr_json, '$.workflow_info.instance_name') LIKE '%齐治堡垒机%' OR
JSON_EXTRACT(t1.attr_json, '$.workflow_info.name') in 'etyer流程' OR
JSON_EXTRACT(t1.attr_json, '$.workflow_info.instance_name') LIKE '存储桶'
)
GROUP BY
t2.audit_user
ORDER BY
审批工单总数 DESC;
布施恩德可便相知重
微信扫一扫打赏
支付宝扫一扫打赏