综合统计.txt

SQL语言常用的一些命令各代码

表a,(商店信息表)
商店名称   商店代码
 aaaa         1
 bbbb         2
 cccc         3
表b,(每天业绩表)
商店代码    销售额    时间
 1           100     2006.10.1
 1           100     2006.10.2
 2           100     2006.10.1
 2           100     2006.10.2
表c,(每月每旬预估表)
商店代码   预估   年/月     旬
 1         500    2006.10   上
 1         500    2006.10   中
 1         500    2006.10   下
求这样的视图
名称,代码,月总额,该月上旬总额,中旬总额,下旬总额,该月预估,上旬预估,中旬预估,下旬预估,年/月

create table a                     /*商店信息表*/
           (商店名称 varchar(8),
            商店代码 smallint)
create table b                     /*每天业绩表*/
           (商店代码 smallint,
            销售额   int,
            时间     varchar(10))
create table c                     /*每月每旬预估表*/
           (商店代码 smallint,
            预估     int,
            [年/月]    varchar(10),
            旬       varchar(5) not null constraint 旬 check(旬  in ('上','中','下') ))
/*插入数据*/
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',   '下'
/*把数据先插入到临时表*/
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.该月预估,0) as 该月预估,
       isnull(xxx.该月上旬预估,0) as 该月上旬预估,
       isnull(xxx.该月中旬预估,0) as 该月中旬预估,
       isnull(xxx.该月下旬预估,0) as 该月下旬预估,
       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 
/*查询预估*/
(select a.商店名称,aaa.商店代码,aaa.月份,aaa.该月预估,isnull(bbb.该月上旬预估,0) as 该月上旬预估,isnull(ccc.该月中旬预估,0)as 该月中旬预估,isnull(ddd.该月下旬预估,0) as 该月下旬预估 from
(select 商店代码,[年/月] as 月份,sum(预估) as 该月预估 from c group by 商店代码,[年/月]) aaa
left join 
(select 商店代码,[年/月] as 月份,sum(预估) as 该月上旬预估 from c where 旬='上' group by 商店代码,[年/月])bbb
on aaa.商店代码=bbb.商店代码 and aaa.月份=bbb.月份
left join 
(select 商店代码,[年/月] as 月份,sum(预估) as 该月中旬预估 from c  where 旬='中' group by 商店代码,[年/月])ccc
on aaa.商店代码=ccc.商店代码 and aaa.月份=ccc.月份
left join 
(select 商店代码,[年/月] as 月份,sum(预估) as 该月下旬预估 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.月份