create table a1(客户名称 varchar(80),商品名称 varchar(20),日期 DateTime,[销量(日)] float)create table a2(客户名称 varchar(80),商品名称 varchar(20),日期 DateTime,[销量(日)] float)create table a3(客户名称 varchar(80),商品名称 varchar(20),日期 DateTime,[销量(日)] float)create table a4(客户名称 varchar(80),商品名称 varchar(20),日期 DateTime,[销量(日)] float)create table a5(客户名称 varchar(80),商品名称 varchar(20),日期 DateTime,[销量(日)] float)create table a6(客户名称 varchar(80),商品名称 varchar(20),日期 DateTime,[销量(日)] float) insert a1 select 'AAAA','a','2006-01-01',100 union all select 'AAAA','a','2006-01-01',120 union all select 'BBBB','b','2006-01-03',150 union all select 'AAAA','b','2006-01-03',200 union all select 'CCCC','c','2006-01-05',100 union all select 'DDDD','d','2006-01-06',130 union all select 'EEEE','c','2006-01-09',120 union all select 'FFFF','d','2006-01-15',100 insert a1 select 'AAAA','a','2006-02-01',100 union all select 'AAAA','a','2006-02-01',120 union all select 'BBBB','b','2006-02-03',150 union all select 'AAAA','b','2006-02-03',200 union all select 'CCCC','c','2006-02-05',100 union all select 'DDDD','d','2006-02-06',130 union all select 'EEEE','c','2006-02-09',120 union all select 'FFFF','d','2006-02-15',100insert a3 select 'AAAA','a','2006-03-01',200 union all select 'AAAA','a','2006-03-01',250 union all select 'BBBB','b','2006-03-03',100 union all select 'AAAA','b','2006-03-03',200 union all select 'CCCC','c','2006-03-05',100 union all select 'DDDD','d','2006-03-06',130 union all select 'EEEE','c','2006-03-09',120 union all select 'FFFF','d','2006-03-15',100 insert a4 select 'AAAA','a','2006-04-01',200 union all select 'AAAA','a','2006-04-01',250 union all select 'BBBB','b','2006-04-03',100 union all select 'AAAA','b','2006-04-03',200 union all select 'CCCC','c','2006-04-05',100 union all select 'DDDD','d','2006-04-06',130 union all select 'EEEE','c','2006-04-09',120 union all select 'FFFF','d','2006-04-15',100 insert a5 select 'AAAA','a','2006-05-01',200 union all select 'AAAA','a','2006-05-01',250 union all select 'BBBB','b','2006-05-03',100 union all select 'AAAA','b','2006-05-03',200 union all select 'CCCC','c','2006-05-05',100 union all select 'DDDD','d','2006-05-06',130 union all select 'EEEE','c','2006-05-09',120 union all select 'FFFF','d','2006-05-15',100 insert a6 select 'AAAA','a','2006-06-01',200 union all select 'AAAA','a','2006-06-01',250 union all select 'BBBB','b','2006-06-03',100 union all select 'AAAA','b','2006-06-03',200 union all select 'CCCC','c','2006-06-05',100 union all select 'DDDD','d','2006-06-06',130 union all select 'EEEE','c','2006-06-09',120 union all select 'FFFF','d','2006-06-15',100测试: declare @t table(客户名称 varchar(80),[销量] float)insert @t select 客户名称,sum([销量(日)]) from a1 group by 客户名称insert @t select 客户名称,sum([销量(日)]) from a2 group by 客户名称insert @t select 客户名称,sum([销量(日)]) from a3 group by 客户名称insert @t select 客户名称,sum([销量(日)]) from a4 group by 客户名称insert @t select 客户名称,sum([销量(日)]) from a5 group by 客户名称insert @t select 客户名称,sum([销量(日)]) from a6 group by 客户名称select top 500 客户名称 from @t group by 客户名称 order by sum([销量]) desc 可以将上面测试的代码写程存储过程即可
yongyupost2000(阿鱼) 少了按商品名称分类,且插入时要给字段命名.改下: ... insert @t select 客户名称,商品名称,sum([销量(日)])as 月销量 from a1 group by 客户名称,商品名称 ... insert @t select 客户名称,商品名称,sum([销量(日)])as 月销量 from a6 group by 客户名称,商品名称select * from @t ss where 客户名称 in ( select top 500 客户名称 from @t where ss.商品名称=商品名称 group by 客户名称 order by sum(月销量) desc )------------- 目前处理是将统计数据插入@t,这样插表代价比较小,但是表@t只能满足你这一类特殊的需求,如果你需要在整年的范围内做很多其他的业务操作,建议将所有的数据都导入一个表中...是不是太大了哈哈
insert a1 select 'AAAA','a','2006-01-01',100
union all select 'AAAA','a','2006-01-01',120
union all select 'BBBB','b','2006-01-03',150
union all select 'AAAA','b','2006-01-03',200
union all select 'CCCC','c','2006-01-05',100
union all select 'DDDD','d','2006-01-06',130
union all select 'EEEE','c','2006-01-09',120
union all select 'FFFF','d','2006-01-15',100
insert a1 select 'AAAA','a','2006-02-01',100
union all select 'AAAA','a','2006-02-01',120
union all select 'BBBB','b','2006-02-03',150
union all select 'AAAA','b','2006-02-03',200
union all select 'CCCC','c','2006-02-05',100
union all select 'DDDD','d','2006-02-06',130
union all select 'EEEE','c','2006-02-09',120
union all select 'FFFF','d','2006-02-15',100insert a3 select 'AAAA','a','2006-03-01',200
union all select 'AAAA','a','2006-03-01',250
union all select 'BBBB','b','2006-03-03',100
union all select 'AAAA','b','2006-03-03',200
union all select 'CCCC','c','2006-03-05',100
union all select 'DDDD','d','2006-03-06',130
union all select 'EEEE','c','2006-03-09',120
union all select 'FFFF','d','2006-03-15',100
insert a4 select 'AAAA','a','2006-04-01',200
union all select 'AAAA','a','2006-04-01',250
union all select 'BBBB','b','2006-04-03',100
union all select 'AAAA','b','2006-04-03',200
union all select 'CCCC','c','2006-04-05',100
union all select 'DDDD','d','2006-04-06',130
union all select 'EEEE','c','2006-04-09',120
union all select 'FFFF','d','2006-04-15',100
insert a5 select 'AAAA','a','2006-05-01',200
union all select 'AAAA','a','2006-05-01',250
union all select 'BBBB','b','2006-05-03',100
union all select 'AAAA','b','2006-05-03',200
union all select 'CCCC','c','2006-05-05',100
union all select 'DDDD','d','2006-05-06',130
union all select 'EEEE','c','2006-05-09',120
union all select 'FFFF','d','2006-05-15',100
insert a6 select 'AAAA','a','2006-06-01',200
union all select 'AAAA','a','2006-06-01',250
union all select 'BBBB','b','2006-06-03',100
union all select 'AAAA','b','2006-06-03',200
union all select 'CCCC','c','2006-06-05',100
union all select 'DDDD','d','2006-06-06',130
union all select 'EEEE','c','2006-06-09',120
union all select 'FFFF','d','2006-06-15',100测试:
declare @t table(客户名称 varchar(80),[销量] float)insert @t
select 客户名称,sum([销量(日)]) from a1
group by 客户名称insert @t
select 客户名称,sum([销量(日)]) from a2
group by 客户名称insert @t
select 客户名称,sum([销量(日)]) from a3
group by 客户名称insert @t
select 客户名称,sum([销量(日)]) from a4
group by 客户名称insert @t
select 客户名称,sum([销量(日)]) from a5
group by 客户名称insert @t
select 客户名称,sum([销量(日)]) from a6
group by 客户名称select top 500 客户名称 from @t
group by 客户名称
order by sum([销量]) desc
可以将上面测试的代码写程存储过程即可
...
insert @t
select 客户名称,商品名称,sum([销量(日)])as 月销量 from a1
group by 客户名称,商品名称
...
insert @t
select 客户名称,商品名称,sum([销量(日)])as 月销量 from a6
group by 客户名称,商品名称select * from @t ss
where 客户名称 in
(
select top 500 客户名称 from @t where ss.商品名称=商品名称
group by 客户名称
order by sum(月销量) desc
)-------------
目前处理是将统计数据插入@t,这样插表代价比较小,但是表@t只能满足你这一类特殊的需求,如果你需要在整年的范围内做很多其他的业务操作,建议将所有的数据都导入一个表中...是不是太大了哈哈