表a,(商店信息表)
商店名稱   商店代碼
 aaaa         1
 bbbb         2
 cccc         3
表b,(每天業(yè)績表)
商店代碼    銷售額    時間
 1           100     2006.10.1
 1           100     2006.10.2
 2           100     2006.10.1
 2           100     2006.10.2
表c,(每月每旬預(yù)估表)
商店代碼   預(yù)估   年/月     旬
 1         500    2006.10   上
 1         500    2006.10   中
 1         500    2006.10   下
求這樣的視圖
名稱,代碼,月總額,該月上旬總額,中旬總額,下旬總額,該月預(yù)估,上旬預(yù)估,中旬預(yù)估,下旬預(yù)估,年/月

create table a                     /*商店信息表*/
           (商店名稱 varchar(8),
            商店代碼 smallint)
create table b                     /*每天業(yè)績表*/
           (商店代碼 smallint,
            銷售額   int,
            時間     varchar(10))
create table c                     /*每月每旬預(yù)估表*/
           (商店代碼 smallint,
            預(yù)估     int,
            [年/月]    varchar(10),
            旬       varchar(5) not null constraint 旬 check(旬  in ('上','中','下') ))
/*插入數(shù)據(jù)*/
insert into  a select  'aaaa',         1
union all      select  'bbbb',         2
union all      select  'cccc',         3

insert into  b select   1,           100,     '2006.10.1'
union all      select   1,           200,     '2006.10.2'
union all      select   1,           520,     '2006.10.12'
union all      select   1,           380,     '2006.10.22'
union all      select   1,           300,     '2006.11.2'
union all      select   1,           300,     '2006.11.2'
union all      select   2,           200,     '2006.11.12'
union all      select   2,           100,     '2006.11.20'
union all      select   2,           150,     '2006.12.3'
union all      select   2,           100,     '2006.12.17'
union all      select   2,           450,     '2006.12.25'

insert into  c select  1,         500,    '2006.10',   '上'
union all      select  1,         500,    '2006.10',   '中'
union all      select  1,         500,    '2006.10',   '下'
insert into  c select  1,         500,    '2006.11',   '上'
union all      select  1,         500,    '2006.3',   '中'
union all      select  1,         500,    '2005.10',   '下'
insert into  c select  2,         500,    '2006.11',   '上'
union all      select  2,         500,    '2006.3',   '中'
union all      select  2,         500,    '2005.10',   '下'
/*把數(shù)據(jù)先插入到臨時表*/
declare @aa table(商店代碼 smallint,
            銷售額   int,
            時間     varchar(10),
            旬       varchar(5))
insert into @aa(商店代碼,銷售額,時間)
            select 商店代碼,銷售額,時間 from b
update @aa set 旬='上' where cast(replace(時間,left(時間,8),'') as int) between 1 and 9
update @aa set 旬='中' where cast(replace(時間,left(時間,8),'') as int) between 10 and 19
update @aa set 旬='下' where cast(replace(時間,left(時間,8),'') as int) between 20 and 31
/*從臨時表中查詢實際情況*/
select isnull(zzz.商店名稱,xxx.商店名稱) as 商店名稱,
       isnull(zzz.商店代碼,xxx.商店代碼)as 商店代碼,
       isnull(zzz.月總額,0)as 月總額,
       isnull(zzz.該月上旬總額,0) as 該月上旬總額,
       isnull(zzz.該月中旬總額,0)as 該月中旬總額,
       isnull(zzz.該月下旬總額,0) as 該月下旬總額,
       isnull(xxx.該月預(yù)估,0) as 該月預(yù)估,
       isnull(xxx.該月上旬預(yù)估,0) as 該月上旬預(yù)估,
       isnull(xxx.該月中旬預(yù)估,0) as 該月中旬預(yù)估,
       isnull(xxx.該月下旬預(yù)估,0) as 該月下旬預(yù)估,
       isnull(zzz.月份,xxx.月份) as 月份
from
(select a.商店名稱,q.商店代碼,q.月總額,isnull(w.該月上旬總額,0) as 該月上旬總額,isnull(e.該月中旬總額,0) as 該月中旬總額,isnull(r.該月下旬總額,0) as 該月下旬總額,q.月份 from
(select 商店代碼,
       left(時間,7)as 月份,
       sum(銷售額)as 月總額
 from @aa   group by 商店代碼,left(時間,7))q
left join 
(select 商店代碼,
       left(時間,7)as 月份,
       sum(銷售額)as 該月上旬總額
 from @aa  where 旬='上' group by 商店代碼,left(時間,7))w
on q.商店代碼=w.商店代碼 and q.月份=w.月份
left join 
(select 商店代碼,
       left(時間,7)as 月份,
       sum(銷售額)as 該月中旬總額
 from @aa  where 旬='中' group by 商店代碼,left(時間,7))e
on q.商店代碼=e.商店代碼 and q.月份=e.月份
left join 
(select 商店代碼,
       left(時間,7)as 月份,
       sum(銷售額)as 該月下旬總額
 from @aa  where 旬='下' group by 商店代碼,left(時間,7))r
on q.商店代碼=r.商店代碼 and q.月份=r.月份
left join a
on 
q.商店代碼=a.商店代碼)zzz
full join 
/*查詢預(yù)估*/
(select a.商店名稱,aaa.商店代碼,aaa.月份,aaa.該月預(yù)估,isnull(bbb.該月上旬預(yù)估,0) as 該月上旬預(yù)估,isnull(ccc.該月中旬預(yù)估,0)as 該月中旬預(yù)估,isnull(ddd.該月下旬預(yù)估,0) as 該月下旬預(yù)估 from
(select 商店代碼,[年/月] as 月份,sum(預(yù)估) as 該月預(yù)估 from c group by 商店代碼,[年/月]) aaa
left join 
(select 商店代碼,[年/月] as 月份,sum(預(yù)估) as 該月上旬預(yù)估 from c where 旬='上' group by 商店代碼,[年/月])bbb
on aaa.商店代碼=bbb.商店代碼 and aaa.月份=bbb.月份
left join 
(select 商店代碼,[年/月] as 月份,sum(預(yù)估) as 該月中旬預(yù)估 from c  where 旬='中' group by 商店代碼,[年/月])ccc
on aaa.商店代碼=ccc.商店代碼 and aaa.月份=ccc.月份
left join 
(select 商店代碼,[年/月] as 月份,sum(預(yù)估) as 該月下旬預(yù)估 from c  where 旬='下' group by 商店代碼,[年/月]) ddd
on aaa.商店代碼=ddd.商店代碼 and aaa.月份=ddd.月份
left join a
on 
aaa.商店代碼=a.商店代碼
) xxx
on zzz.商店代碼=xxx.商店代碼 and zzz.月份=xxx.月份