一个纵表变横表的优化过程

在一个已经实放的项目中，有一个纵表变横表的需求，实现代码如下：

 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

此表记录每天以几何级数增长，结果是客户一再反映太慢。经过测试，确实慢，首次执行３分钟，再次执行３秒。

决定优化，代码如下：

select  a.FProjectNO, MAX (a.FProjectName),  2   as  FRegister,  MAX (a.FRegisterDate)  as  FInsertedDate,
        MAX ( case   when  b.FIsVised  =   1   AND  FNodeID  =   11402   then   2   else   0   end )  as  FTrading,
        MAX ( case   when  b.FIsVised  =   1   and  FNodeID  =   11403   then   2   else   0   end )  as  FPlanChecking,
        MAX ( case   when  b.FIsVised  =   1   AND  FNodeID  =   11405   then   2   else   0   end )  as  FSafety,
        MAX ( case   when  b.FIsVised  =   1   AND  FNodeID  =   11406   then   2   else   0   end )  as  FQuality,
        MAX ( case   when  b.FIsVised  =   1   AND  FNodeID  =   11407   then   2   else   0   end )  as  FCost,
        MAX ( case   when  b.FIsVised  =   1   AND  FNodeID  =   11408   then   2   else   0   end )  as  FAudit,
        MAX ( case   when  b.FIsVised  =   1   AND  FNodeID  =   11409   then   2   else   0   end )  as  FArrears,
        MAX ( case   when  b.FIsVised  =   1   AND  FNodeID  =   11414   then   2   else   0   end )  as  FTender,
        MAX ( case   when  b.FApproveStatus  %   20   =   10   AND  FNodeID  =   11410   then   2   else   0   end )  as  FLicence
   from  PRO_BaseInfo a 
left   outer   join     PRO_NodeStatus b  ON  b.FProjectNO  =  a.FProjectNO   
   where  a.FStatus  =   70   and  a.FIsDeleted  =   0  
group   by  a.FProjectNO

执行结果为2秒，优化成功。

此思路为经老五再三埋汰的条件下指点而成。

开始时没有用Group，结果是多条记录。

这种方法以前用过，离代码远了，思路不开阔了。

转载于:https://www.cnblogs.com/Sabre/archive/2008/04/16/1156647.html