DBeaver-查询工单

选择这个数据库

打开，选择这个进行连接

选择这个

按照图片进行连接

设为活动对象

工单是这个

选择下面这个 可以建立SQL编辑窗口

输入

select count(*) from tb_work_order where is_delete=0 and company_id= '602531310603714560';

然后就能显示出来

如果查看所有工单的详情则

#所有工单
select * from tb_work_order where is_delete=0 and company_id= '602531310603714560';

数据库中is_delete，0代表未删除，1代表删除，默认值为0

已完成工单

#已完成工单
select * from tb_work_order where is_delete=0 and order_status=6 and company_id= '602531310603714560';

order_status各个值的意思

待完成工单总数统计（除去已完成和已终止）

#待完成工单总数统计
select * from tb_work_order where is_delete=0 and order_status!=7 and order_status!=6 and company_id= '602531310603714560';

例行维保和应急维保

待完成应急维保工单总数统计（除去已完成和已终止）

#待完成应急工单总数统计
select * from tb_work_order where is_delete=0 and order_status!=7 and order_status!=6 and company_id= '602531310603714560' and type=2;

每日应急工单数量统计

#每日应急工单数量统计
select * from tb_work_order where is_delete=0 and company_id= '602531310603714560' and type=2 and create_time > '2021-06-09 00:00:00' and 
create_time < '2021-06-09 23:59:59';

每日例行工单数量统计

#每日例行工单数量统计
select * from tb_work_order where is_delete=0 and company_id= '602531310603714560' and type=1 and create_time > '2021-06-09 00:00:00' and 
create_time < '2021-06-09 23:59:59';

运行商 服务单位

company_id为运营商yk
unit_id为服务单位

工单完成率统计(以康师傅为例)

#康师傅工单完成率统计
select count(*) as a from tb_work_order where is_delete=0 and company_id= '602531310603714560'  and unit_id=603957085827612672 and (order_status=7 or order_status=6);
select count(*) as b from tb_work_order where is_delete=0 and company_id= '602531310603714560'  and unit_id=603957085827612672;
select 1.0/2;

近30天的所有工单

#近30天的所有工单
select * from tb_work_order where is_delete=0 and company_id= '602531310603714560' and start_date > '2021-05-10 00:00:00' and 
start_date < '2021-06-10 23:59:59';

近30天的待完成工单

#近30天的待完成工单
select * from tb_work_order where is_delete=0 and company_id= '602531310603714560' and start_date > '2021-05-10 00:00:00' and 
start_date < '2021-06-10 23:59:59' and order_status!=7 and order_status!=6;

近30天的待完成应急工单

#近30天的待完成应急工单
select * from tb_work_order where is_delete=0 and company_id= '602531310603714560' and start_date > '2021-05-10 00:00:00' and 
start_date < '2021-06-10 23:59:59' and order_status!=7 and order_status!=6 and type=2;

字段说明：