1DECLARE @time_begin datetime 
 2DECLARE @time_end datetime 
 3DECLARE @buytime_begin datetime 
 4DECLARE @buytime_end datetime 
 5 
 6SET  @time_begin='2016/2/1'      -- 可修改变量   (   @time_begin<=shopdetial时间区间< @time_end  
 7SET  @time_end='2016/3/1'        --可修改变量   (
 8 
 9SET  @buytime_begin='2015/12/1'   --可修改变量   (  @buytime_begin<=buydetial时间区间< @buytime_end  
10SET  @buytime_end='2016/3/1'     --可修改变量   (
11 
12 
13 
14--SELECT @time_begin,@time_end
15 
16 
17SELECT customer.cardno AS '会员号码',customer.tel AS '电话',Temp.t1 AS '区间累计物品',ISNULL(temp2.m1,0) AS '区间累计消费' 
18 
19FROM 
20 
21(SELECT cardno AS l1,sum(amount) AS t1 FROM shopdetial WHERE busydate >=@time_begin AND busydate <@time_end GROUP BY cardno ) Temp 
22--(SELECT TOP 200 cardno AS l1,sum(amount) AS t1 FROM shopdetial WHERE busydate >=@time_begin AND busydate <@time_end GROUP BY cardno ORDER BY t1 desc) Temp 
23LEFT JOIN
24 
25(SELECT  cardno AS l1,sum(money) AS m1 FROM buydetial WHERE busydate >=@buytime_begin  AND busydate <@buytime_end AND cardno<>'' AND seller_no<>'LIPIN2' GROUP BY cardno) temp2 
26 
27ON  Temp.l1=temp2.l1 
28 
29LEFT JOIN customer 
30 
31ON Temp.l1=customer.cardno 
32 
33ORDER BY t1 desc