select * from 
(select 客户ID,点击消费,date from datasource where date>getdate()-32)a 
pivot(max(点击消费) for date in([2012-9-12],[2012-9-13]))b
left join 
(select 客户ID,avg(hittime) as hittime from downaccount
where date >=getdate()-32
group by 客户ID)c on b.客户ID=c.客户ID
left join
(select 客户ID,case when sum(点击次数)=0 then 0 else sum(点击消费)/sum(点击次数) end as acp
from datasource st where st.date >=getdate()-32
group by 客户ID)d on b.客户ID=d.客户ID

declare @date datetime = getdate()-32

with tb as
(
select *, ISNULL(d.acp, 0) from 
(select 客户ID,点击消费,date from datasource where date>@date)a 
pivot(max(点击消费) for date in([2012-9-12],[2012-9-13]))b
)
select * from tb
left join 
(select 客户ID,avg(hittime) as hittime from downaccount
where date >=@date
group by 客户ID)c on b.客户ID=c.客户ID
left join
(select 客户ID,sum(点击消费)/sum(点击次数) as acp
from datasource st where st.date >=@date and sum(点击次数)>0
group by 客户ID)d on b.客户ID=d.客户ID

------解决方案--------------------
首先可以先合并掉一个left join
我在看看PIVOT  
SQL code
SELECT  *
FROM    (
         SELECT 客户ID , 点击消费 , date FROM datasource WHERE date> GETDATE ()- 32
        ) a PIVOT ( MAX(点击消费) FOR date IN ([2012-9-12] , [2012-9-13]) ) b
LEFT JOIN (
           SELECT 客户ID , AVG(hittime) AS hittime,CASE WHEN SUM(点击次数) = 0 THEN 0
                              ELSE SUM(点击消费)/SUM(点击次数)
                         END AS acp
           FROM   downaccount
           WHERE  date >= GETDATE()-32
           GROUP BY 客户ID
          ) c
ON      b.客户ID = c.客户ID

------解决方案--------------------
再加一点，
4. 最后select的时候，别用select *,而是把你需要的列写出来。

------解决方案--------------------
貌似没有什么地方改进了
你看下  
SELECT  *
FROM    (
        SELECT 客户ID , 点击消费 , date FROM datasource WHERE date> GETDATE ()- 32
       ) a PIVOT ( MAX(点击消费) FOR date IN ([2012-9-12] , [2012-9-13]) ) b

和  
  SELECT 客户ID , AVG(hittime) AS hittime,CASE WHEN SUM(点击次数) = 0 THEN 0
                             ELSE SUM(点击消费)/SUM(点击次数)
                        END AS acp
          FROM   downaccount
          WHERE  date >= GETDATE()-32
          GROUP BY 客户ID

分别用多久

把PIVOT 改成case when 测一下又是什么情况