SQL联表去重查询和优化

项目背景

一个工单管理系统，工单处理流程（简化后）是： 创建工单 -> 分派工单 -> 处理工单 -> 完成工单。

表结构

数据库用的是SQL Server（MySQL其实也类似），有两张表： 工单表Order 和 工单记录表OrderRecord。 工单记录表里面记录者每张工单的操作记录，是一对多的关系。

工单表Order：

• id是自增id
• title是工单的标题
• status是工单当前状态， 1(待处理), 2(处理中), 3(已完成)

工单记录表OrderRecord ：

• id是自增id
• recordType记录类型的枚举，有：1(创建工单), 2(分派工单), 3(处理工单), 4(完成工单)。
• operator操作者名字。
• orderId该记录所属的工单id，对应Order表的id字段。
• createdAt 记录创建时间。
• remark 备注信息。

查询一张工单的操作记录，如查询工单id为1的操作记录： select * from OrderRecord where orderId=1，配合程序得到的结果如下：

通过这个操作记录可以看到，工单处理完成后，可能由于一些特殊原因处理地不好，这张工单中途被李四重新分派出去了，然后由王五进行了完工。

需求

为了统计员工的工作量来做绩效管理，所以老板想要统计每个月员工的工单完工情况，需要导出一个数据报表。
因为特殊原因，工单的分派者的工作量很大，所以老板希望在统计报表里面看到工单分派员是谁（工单有多次分派情况时以最后一次分派为准），报表内容大概如下：

需求应该很容易理解，就是查询某个月的完工单的情况，而且需要从工单的记录表里面找到最新的一个分派员，这个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为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，这样就可以查出最后的结果集了：

优化

美滋滋地完成了需求，这么难的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; 

×

fengxianqi

(๑＞ڡ＜)☆谢谢老板~

2元 5元 10元 50元 100元 任意金额

2元

使用微信扫描二维码完成支付