left join 优化

原来的:

SELECT
 M.clientid, 
 M.CardFaceID, 
 N.NormalBanalce, 
 D.DateWorth, 
 T.TimesWorth, 
 B.BookingWorth,
 B.BookingTimesManyBalance
FROM  
    ( 
 SELECT
  clientid,CardFaceID
 FROM 
  cimain 
    ) M 
 
 Left Join
  (SELECT 
   clientid,
   sum( case when IfGive='是' then Balance * ItemZkl else Balance end) as NormalBanalce  
  FROM ccNormal   
  Group By  clientid  ) N on M.clientid=N.clientid 


 Left Join          
  (SELECT 
   clientid,
   sum(   ConsumeBalance * ItemZkl  )  as DateWorth
  FROM ccDate    
  Group By  clientid  ) D on M.clientid=D.clientid 

 Left Join
  (SELECT 
   clientid,
   sum(   AveragePrice * TimesBalance * ItemZKL   )  as TimesWorth      
  FROM ccTimes   
  Group By  clientid  ) T on M.clientid=T.clientid 

 Left Join
  (SELECT 
   clientid,
   sum(   PriceDiscount * TimesBalance   )  as BookingWorth,
   sum(TimesBalance) as BookingTimesManyBalance         
  FROM ccBooking   
  Group By  clientid  ) B on M.clientid=B.clientid

优化后:

SELECT
    M.clientid  , 
    M.CardFaceID, 
    (SELECT sum(case IfGive when '是' then Balance*ItemZkl else Balance end) FROM ccNormal WHERE clientid=M.clientid) AS NormalBanalce,
    (SELECT sum(ConsumeBalance*ItemZkl) FROM ccDate WHERE clientid=M.clientid) AS DateWorth, 
    (SELECT sum(AveragePrice*TimesBalance*ItemZKL) FROM ccTimes WHERE clientid=M.clientid) AS TimesWorth, 
    (SELECT sum(PriceDiscount*TimesBalance) FROM ccBooking WHERE clientid=M.clientid) AS BookingWorth,
    (SELECT sum(TimesBalance) FROM ccBooking WHERE clientid=M.clientid) AS BookingTimesManyBalance
FROM  
    cimain M

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页