项目背景
一个工单管理系统,工单处理流程(简化后)是: 创建工单 -> 分派工单 -> 处理工单 -> 完成工单。
表结构
数据库用的是SQL Server(MySQL其实也类似),有两张表: 工单表Order
和 工单记录表OrderRecord
。 工单记录表里面记录者每张工单的操作记录,是一对多的关系。
工单表Order
:
id | title | status |
---|---|---|
1 | 工单1 | 2 |
2 | 工单2 | 1 |
3 | 工单3 | 3 |
id
是自增idtitle
是工单的标题status
是工单当前状态, 1(待处理), 2(处理中), 3(已完成)
工单记录表OrderRecord
:
id | recordType | operator | orderId | createdAt | remark |
---|---|---|---|---|---|
1 | 1 | 张三 | 1 | 2021-07-01 | |
2 | 2 | 李四 | 1 | 2021-07-02 | |
3 | 3 | 张三 | 1 | 2021-07-03 | |
4 | 4 | 张三 | 1 | 2021-07-04 | |
5 | 2 | 李四 | 1 | 2021-07-05 | 需要返工,重新分派工单 |
6 | 1 | 王五 | 2 | 2021-07-05 | |
7 | 3 | 王五 | 1 | 2021-07-06 | |
8 | 4 | 王五 | 1 | 2021-07-07 |
id
是自增idrecordType
记录类型的枚举,有:1(创建工单), 2(分派工单), 3(处理工单), 4(完成工单)。operator
操作者名字。orderId
该记录所属的工单id,对应Order
表的id字段。createdAt
记录创建时间。remark
备注信息。
查询一张工单的操作记录,如查询工单id为1的操作记录: select * from OrderRecord where orderId=1
,配合程序得到的结果如下:
记录ID | 操作类型 | 操作人 | 操作时间 | 备注 |
---|---|---|---|---|
1 | 创建工单 | 张三 | 2021-07-01 | |
2 | 分派工单 | 李四 | 2021-07-02 | |
3 | 处理工单 | 张三 | 2021-07-03 | |
4 | 完成工单 | 张三 | 2021-07-04 | |
5 | 分派工单 | 李四 | 2021-07-05 | 需要返工,重新分派工单 |
7 | 处理工单 | 王五 | 2021-07-06 | |
7 | 完成工单 | 李四 | 2021-07-07 |
通过这个操作记录可以看到,工单处理完成后,可能由于一些特殊原因处理地不好,这张工单中途被李四
重新分派出去了,然后由王五
进行了完工。
需求
为了统计员工的工作量来做绩效管理,所以老板想要统计每个月员工的工单完工情况,需要导出一个数据报表。
因为特殊原因,工单的分派者
的工作量很大,所以老板希望在统计报表里面看到工单分派员是谁(工单有多次分派情况时以最后一次分派为准),报表内容大概如下:
工单ID | 工单名称 | 完工时间 | 分派员 |
---|---|---|---|
1 | 工单1 | 2021-07-07 | 李四 |
3 | 工单3 | 2021-07-08 | 张三 |
需求应该很容易理解,就是查询某个月的完工单的情况,而且需要从工单的记录表里面找到最新的一个分派员,这个SQL语句应该怎么写呢?
需求实现
工单的完工时间和分派员需要从 OrderRecord
表里面取值,而且一张工单会有多次分派、多次完工的情况,比较复杂。我们一步一步来实现这个过程。
1. 查询所有的完工单
通过表的定义,我们知道,当status=3时表示工单已完工了,所以我们首先实现查询所有的完工单:
select * from Order status=3
2. 查询某个月份的完工单
每个月的完工单,我们暂时不考虑给Order表加字段的情况,而是通过 OrderRecord 进行联表查询。
假设每个工单只能完工一次(即recordType=4的情况只会出现一次),那么我们可以很轻松地联表查询去做到,SQL这样写就可以了:
select * from Order o
left join OrderRecord or on or.orderId=o.id
where
o.status=3 and or.recordType=4 and or.createdAt between '2021-07-01' and '2021-08-01'
但是,咱们这里的工单是允许完工多次的,上面这条语句会导致出现多条工单id一样的记录,而且没法用distinct去重,所以上面这条SQL语句没法满足需求。
上面知道工单1
有两次完工的记录,我们查询该工单的完工记录的SQL语句如下:
select * from OrderRecord where ordeId=1 and recordType=4
得到的结果如下:
id | recordType | operator | orderId | createdAt | remark |
---|---|---|---|---|---|
4 | 4 | 张三 | 1 | 2021-07-04 | |
8 | 4 | 王五 | 1 | 2021-07-07 |
我们的需求是:实际的完工时间是取最后一次完工时间,所以要取记录ID为8的这一条作为该工单的完工时间。即按时间倒序排取第一条: select top 1 * from OrderRecord where ordeId=1 and recordType=4 order by createdAt desc
。
但如果和工单表联表查询的话没法使用top 1
这种写法呀,所以笔者查到了 SQL Server内部提供了max
方法,可用于按最大值来排序。
最后查询查询某个月份的完工单的SQL语句如下:
select o.id, o.title, or.createdAt from Order o
left join (select recordType,max(createdAt) as createdAt,orderId from OrderRecord where recordType=4 group by recordType,orderId) or on or.orderId=o.id
where
o.status=3 and or.createdAt between '2021-07-01' and '2021-08-01'
3. 查询某个月份的完工单的分派员
由于工单允许多次分派、多次完工,让我们的SQL语句写得多么曲折,怕了怕了。。。
上一步用select recordType,max(createdAt) as createdAt,orderId from OrderRecord where recordType=4 group by recordType,orderId
来实现了完工时间的去重,这次我们要实现分派员
的去重。
依样画葫芦可以吗?比如分派员的语句我们尝试这样用(recordType=2表示分派工作):
select recordType,max(createdAt) as createdAt,orderId,operator from OrderRecord where recordType=2 group by recordType,orderId,operator
由于我们加了一个 operator的字段,导致 group by
也没法去重了,所以没法依样画葫芦。
咱们换个思路:先查询出去重后的分派记录的id,然后再查询分派员的名字,即:
select operator,orderId from OrderRecord where id in (
select id from (
select max(id) as id,orderId from OrderRecord where recordType=2 group by recordType,orderId
) tempIds
)
从最中间的语句查询出去重后的记录id临时表,然后再从这个临时表里面得到操作者等字段。最终的查询某个月份的完工单的分派员的SQL语句如下:
select o.id, o.title, or.createdAt,ocs.operator as orderSender from Order o
left join (select recordType,max(createdAt) as createdAt,orderId from OrderRecord where recordType=4 group by recordType,orderId) or on or.orderId=o.id
left join (
select operator,orderId from OrderRecord where id in (
select id from (
select max(id) as id,orderId from OrderRecord where recordType=2 group by recordType,orderId
) tempIds
)
) as ors on ors.orderId=o.id
where
o.status=3 and or.createdAt between '2021-07-01' and '2021-08-01' and ocs.operator='张三'
ocs.operator as orderSender
: 查询分派员的记录临表别名是ocs
,避免命名冲突分派员的字段别名取了orderSender
,这样就可以查出最后的结果集了:
id | title | createdAt | orderSender |
---|---|---|---|
1 | 工单1 | 2021-07-07 | 李四 |
3 | 工单3 | 2021-07-08 | 张三 |
优化
美滋滋地完成了需求,这么难的SQL都写出来了,测试也完全ok,信心膨胀到极点,然后上线就翻车了。
生产的工单表有1w多条,工单记录表20多w条,上面那条SQL语句执行时间需要接近 20秒,20秒啊,整个人都傻掉了。
OrderRecord表之前有对orderId做了索引,理论上速度会快一些的。
上面那条SQL语句为了兼顾分派员的查询,实在是太慢了,嵌套太多,需要想办法优化。
这个SQL已经想不到啥优化办法了,所以笔者想到的一个办法是在Order表新增一个分派员的字段,这样就可以不用去重联表查了,在分派员操作时同时多记录操作者到Order表,这样以空间换时间的思路来实现。
即Order表新增一个orderSender
字段:alter table Order add orderSender varchar(20) null
,查询语句可以简化为:
select o.id, o.title, or.createdAt,o.orderSender from Order o
left join (select recordType,max(createdAt) as createdAt,orderId from OrderRecord where recordType=4 group by recordType,orderId) or on or.orderId=o.id
where
o.status=3 and or.createdAt between '2021-07-01' and '2021-08-01' and o.operator='张三'
试了一下,上面语句的执行时间基本可以限制在1秒左右就完成,速度可以提升95%以上。
进一步优化的话,我们可以把完工时间也放到Order表,这样就可以完全做到不链表进行查询了。原理是一样的,这里就不写这部分了。
新增了一个字段,但我们需要解决存量数据的问题,即需要把以前的工单分派员更新到新增的orderSender
字段中,用这个语句:
update Order set orderSender=oc.operator from (
select operator,orderId from OrderRecord where id in (
select id from (
select max(id) as id,orderId from OrderRecord where recordType=2 group by recordType,orderId
) tempIds
)
) oc where oc.orderId=Order.id
PS: 之前没有想到上面这个语句,所以笔者折腾了一下存储过程,即将查询出来的记录id,通过循环去一个个update,但效率很慢,执行了好久好久。
存储过程的写法如下:
CREATE PROCEDURE p_orderSender as
BEGIN
DECLARE
@operator VARCHAR(20),
@orderId INT
-- 定义游标
DECLARE cur CURSOR FOR
select operator,orderId from OrderRecord where id in (
select id from (
select max(id) as id,orderId from OrderRecord where recordType=2 group by recordType,orderId
) tempIds
)
OPEN cur
fetch next from cur into @operator,@orderId
while @@fetch_status<>-1
begin
--您要执行的操作写在这里
update Order set orderSender=@operator where id=@orderId
fetch next from cur into @operator,@orderId
end
close cur
deallocate cur
END;
「一键投喂 软糖/蛋糕/布丁/牛奶/冰阔乐!」
(๑>ڡ<)☆谢谢老板~
使用微信扫描二维码完成支付