39 lines
2.9 KiB
SQL
39 lines
2.9 KiB
SQL
-- 异常单排查
|
|
SELECT q.充电站名称, q.充电枪编码,t1.零元单下单时间 异常单下单时间,t2.成功单下单时间,
|
|
TIMESTAMPDIFF(day,t2.成功单下单时间,t1.零元单下单时间) 异常天数,
|
|
-- TIMESTAMPDIFF(HOUR,t2.成功单下单时间,t1.零元单下单时间) 异常小时,
|
|
TIMESTAMPDIFF(day,t1.零元单下单时间,now()) 最近异常单偏离天数,
|
|
TIMESTAMPDIFF(day,t2.成功单下单时间,now()) 无收入天数,
|
|
q.设备商名称 ,q.充电枪类型,q.充电枪状态
|
|
FROM testdb.qiang q
|
|
LEFT JOIN (select max(a.下单时间) 零元单下单时间,a.充电终端,a.充电站 from testdb.dingdan2025 a where a.应收订单金额 = 0 and a.订单状态 != '充电中' group by a.充电终端 order by a.充电终端) t1
|
|
ON q.充电枪编码 = t1.充电终端
|
|
LEFT join (select max(a.下单时间) 成功单下单时间,a.充电终端,a.充电站 from testdb.dingdan2025 a where a.应收订单金额 != 0 and a.订单状态 != '充电中' group by a.充电终端 order by a.充电终端) t2
|
|
on q.充电枪编码 = t2.充电终端
|
|
where (TIMESTAMPDIFF(day,t2.成功单下单时间,t1.零元单下单时间) > 0
|
|
or TIMESTAMPDIFF(day,t2.成功单下单时间,t1.零元单下单时间) is null)
|
|
or TIMESTAMPDIFF(day,t2.成功单下单时间,now()) > 0
|
|
or q.充电枪状态 = '未登录'
|
|
or q.充电枪状态 = '故障维修中'
|
|
-- and q.充电枪状态 != '空闲(已登录)' and q.充电枪状态 != '充电中'
|
|
-- and q.充电站名称 like '%白云%'
|
|
order by q.设备商名称,q.充电站名称,q.充电枪编码,t2.成功单下单时间 ;
|
|
|
|
|
|
|
|
-- 全站排查
|
|
SELECT q.充电站名称, q.充电枪编码,t1.零元单下单时间 异常单下单时间,t2.成功单下单时间,
|
|
TIMESTAMPDIFF(day,t2.成功单下单时间,t1.零元单下单时间) 异常天数,
|
|
TIMESTAMPDIFF(HOUR,t2.成功单下单时间,t1.零元单下单时间) 异常小时,
|
|
TIMESTAMPDIFF(day,t1.零元单下单时间,now()) 最近异常单偏离天数,
|
|
q.设备商名称 ,q.充电枪类型,q.充电枪状态
|
|
FROM testdb.qiang q
|
|
LEFT JOIN (select max(a.下单时间) 零元单下单时间,a.充电终端,a.充电站 from testdb.dingdan2025 a where a.应收订单金额 = 0 group by a.充电终端 order by a.充电终端) t1
|
|
ON q.充电枪编码 = t1.充电终端
|
|
LEFT join (select max(a.下单时间) 成功单下单时间,a.充电终端,a.充电站 from testdb.dingdan2025 a where a.应收订单金额 != 0 group by a.充电终端 order by a.充电终端) t2
|
|
on q.充电枪编码 = t2.充电终端
|
|
|
|
-- where (TIMESTAMPDIFF(day,t2.成功单下单时间,t1.零元单下单时间) > 0 or TIMESTAMPDIFF(day,t2.成功单下单时间,t1.零元单下单时间) is null)
|
|
-- and q.充电枪状态 != '空闲(已登录)' and q.充电枪状态 != '充电中'
|
|
where q.充电站名称 like '%南宁交投能源凤岭客运站充电站'
|
|
order by q.设备商名称,q.充电站名称,q.充电枪编码,t2.成功单下单时间 ; |