一个纵表变横表的优化过程
在一个已经实放的项目中,有一个纵表变横表的需求,实现代码如下:
1
2
select a.FProjectNO, a.FProjectName, 2 as FRegister, a.FRegisterDate as FInsertedDate,
3
case b02.FIsVised when 1 then 2 else 0 end as FTrading,
4
case b03.FIsVised when 1 then 2 else 0 end as FPlanChecking,
5
case b14.FIsVised when 1 then 2 else 0 end as FTender,
6
case b06.FIsVised when 1 then 2 else 0 end as FQuality,
7
case b05.FIsVised when 1 then 2 else 0 end as FSafety,
8
case b07.FIsVised when 1 then 2 else 0 end as FCost,
9
case b08.FIsVised when 1 then 2 else 0 end as FAudit,
10
case b09.FIsVised when 1 then 2 else 0 end as FArrears,
11
case b10.FApproveStatus % 20 when 10 then 2 else 0 end as FLicence
12
from PRO_BaseInfo a left outer join
13
PRO_NodeStatus b02 on b02.FProjectNO = a.FProjectNO and b02.FNodeID = 11402 left outer join
14
PRO_NodeStatus b03 on b03.FProjectNO = a.FProjectNO and b03.FNodeID = 11403 left outer join
15
PRO_NodeStatus b14 on b14.FProjectNO = a.FProjectNO and b14.FNodeID = 11414 left outer join
16
PRO_NodeStatus b06 on b06.FProjectNO = a.FProjectNO and b06.FNodeID = 11406 left outer join
17
PRO_NodeStatus b05 on b05.FProjectNO = a.FProjectNO and b05.FNodeID = 11405 left outer join
18
PRO_NodeStatus b07 on b07.FProjectNO = a.FProjectNO and b07.FNodeID = 11407 left outer join
19
PRO_NodeStatus b08 on b08.FProjectNO = a.FProjectNO and b08.FNodeID = 11408 left outer join
20
PRO_NodeStatus b09 on b09.FProjectNO = a.FProjectNO and b09.FNodeID = 11409 left outer join
21
PRO_NodeStatus b10 on b10.FProjectNO = a.FProjectNO and b10.FNodeID = 11410
22
where a.FStatus = 70 and a.FIsDeleted = 0

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

此表记录每天以几何级数增长,结果是客户一再反映太慢。经过测试,确实慢,首次执行3分钟,再次执行3秒。
决定优化,代码如下:














执行结果为2秒,优化成功。
此思路为经老五再三埋汰的条件下指点而成。
开始时没有用Group,结果是多条记录。
这种方法以前用过,离代码远了,思路不开阔了。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步