SQL联表去重查询和优化

本文共有6635个字,关键词:sql联表查询存储过程

项目背景

一个工单管理系统,工单处理流程(简化后)是: 创建工单 -> 分派工单 -> 处理工单 -> 完成工单。

表结构

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

工单表Order

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

工单记录表OrderRecord

idrecordTypeoperatororderIdcreatedAtremark
11张三12021-07-01
22李四12021-07-02
33张三12021-07-03
44张三12021-07-04
52李四12021-07-05需要返工,重新分派工单
61王五22021-07-05
73王五12021-07-06
84王五12021-07-07
  • id是自增id
  • recordType记录类型的枚举,有: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工单12021-07-07李四
3工单32021-07-08张三

需求应该很容易理解,就是查询某个月的完工单的情况,而且需要从工单的记录表里面找到最新的一个分派员,这个SQL语句应该怎么写呢?

需求实现

工单的完工时间和分派员需要从 OrderRecord 表里面取值,而且一张工单会有多次分派、多次完工的情况,比较复杂。我们一步一步来实现这个过程。

  1. 查询所有的完工单

通过表的定义,我们知道,当status=3时表示工单已完工了,所以我们首先实现查询所有的完工单:

select * from Order status=3
  1. 查询某个月份的完工单

每个月的完工单,我们暂时不考虑给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

得到的结果如下:

idrecordTypeoperatororderIdcreatedAtremark
44张三12021-07-04
84王五12021-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'
  1. 查询某个月份的完工单的分派员
    由于工单允许多次分派、多次完工,让我们的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,这样就可以查出最后的结果集了:

idtitlecreatedAtorderSender
1工单12021-07-07李四
3工单32021-07-08张三

优化

美滋滋地完成了需求,这么难的SQL都写出来了,测试也完全ok,信心膨胀到极点,然后上线就翻车了。
生产的工单表有1w多条,工单记录表20多w条,上面那条SQL语句执行时间需要接近 20秒,20秒啊,整个人都傻掉了。
OrderRecord表之前有对orderId做了索引,理论上速度会快一些的。

上面那条SQL语句为了兼顾分派员的查询,实在是太慢了,嵌套太多,需要想办法优化。

这个SQL已经想不到啥优化办法了,所以笔者想到的一个办法是在Rrder表新增一个分派员的字段,这样就可以不用去重联表查了,在分派员操作时同时多记录操作者到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

(๑>ڡ<)☆谢谢老板~

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

版权声明:本文为作者原创,如需转载须联系作者本人同意,未经作者本人同意不得擅自转载。
添加新评论
暂无评论