原来的:
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