declare @开始时间 varchar(10),@结束时间 varchar(10)
select @开始时间='2003-10-14',@结束时间='2003-10-16'declare @s varchar(8000)
set @s=''
select @s=@s+','+type_name+'=sum(case type_id when '
+cast(id as varchar)+' then [money] else 0 end)'
from 表A
exec('select 编号=a.cmp_id
,公司名称=case when grouping(b.name)=1 then ''-合计-'' else b.name end'
+@s+',合计=sum(money)
from 表b a inner join 表C b on a.cmp_id=b.cmp_id where date between '''+@开始时间+''' and '''+@结束时间+'''
group by a.cmp_id,b.name with rollup
having grouping(b.name)=0 or grouping(a.cmp_id)=1')
go
drop table 表a,表b,表c
create table 表A(id int,type_name varchar(10))
insert into 表A
select 1,'test1'
union all select 2,'test2'
union all select 3,'test3'create table 表B(id int,type_id int,cmp_id int,[money] int,date datetime)
insert into 表B
select 1,1,1,20,'2003-10-12'
union all select 2,1,1,30,'2003-10-15'
union all select 3,2,2,32,'2003-10-15'
union all select 4,2,3,33,'2003-10-15'
union all select 3,1,2,52,'2003-10-15'
union all select 4,3,3,63,'2003-10-15'create table 表C(cmp_id int,name varchar(10))
insert into 表C
select 1,'A公司'
union all select 2,'B公司'
union all select 3,'C公司'
union all select 4,'D公司'
union all select 5,'E公司'declare @s varchar(8000)
set @s=''
select @s=@s+','+type_name+'=sum(case type_id when '
+cast(id as varchar)+' then [money] else 0 end)'
from 表A
exec('select 编号=a.cmp_id
,公司名称=case when grouping(b.name)=1 then ''-合计-'' else b.name end'
+@s+',合计=sum(money)
from 表b a inner join 表C b on a.cmp_id=b.cmp_id
where b.date between 时间1 and 时间2 -- (在此处加入限制条件)
group by a.cmp_id,b.name with rollup
having grouping(b.name)=0 or grouping(a.cmp_id)=1')
go
drop table 表a,表b,表c
create table 表A(id int,type_name varchar(10))
insert into 表A
select 1,'test1'
union all select 2,'test2'
union all select 3,'test3'create table 表B(id int,type_id int,cmp_id int,[money] int,date datetime)
insert into 表B
select 1,1,1,20,'2003-10-12'
union all select 2,1,1,30,'2003-10-15'
union all select 3,2,2,32,'2003-10-15'
union all select 4,2,3,33,'2003-10-15'
union all select 3,1,2,52,'2003-10-15'
union all select 4,3,3,63,'2003-10-15'create table 表C(cmp_id int,name varchar(10))
insert into 表C
select 1,'A公司'
union all select 2,'B公司'
union all select 3,'C公司'
union all select 4,'D公司'
union all select 5,'E公司'go
--创建数据处理的存储过程:
create proc p_qry
@dt1 datetime, --开始时间
@dt2 datetime --结束时间
as
declare @s varchar(8000),@tj varchar(8000)
set @s=''
select @s=@s+','+type_name+'=sum(case type_id when '
+cast(id as varchar)+' then [money] else 0 end)'
from 表Aset @tj='a.date between '''+convert(varchar(10),@dt1,120)
+''' and '''++convert(varchar(10),@dt2,120)+''''
exec('select 编号=a.cmp_id
,公司名称=case when grouping(b.name)=1 then ''-合计-'' else b.name end'
+@s+',合计=sum(money)
from 表b a inner join 表C b on a.cmp_id=b.cmp_id
where '+@tj+'
group by a.cmp_id,b.name with rollup
having grouping(b.name)=0 or grouping(a.cmp_id)=1')
go--调用存储过程得到结果:
exec p_qry '2003-10-12','2003-10-15'
go
--删除测试环境
drop table 表a,表b,表c
drop proc p_qry
set rs=server.createobject("adodb.recordset")
rs.open "exec p_qry '2003-10-12','2003-10-15'",数据库连接字符串
...
%>
optype 表A 类型
编号 id int
名称 type_name varchar(100)opreport 表B 类型
编号 id int
业务种类 type_id int
公司 cmp_id int
金额 money money
日期 date datetimeopcompany 表C 类型
编号 cmp_id int
公司全称 name varchar(60)然后我改写了邹建的代码:declare @s varchar(8000)
set @s=''
select @s=@s+','+名称+'=sum(case 业务种类 when '
+cast(编号 as varchar)+' then 金额 else 0 end)'
from optype
exec('select 编号=a.代理商
,公司名称=case when grouping(b.公司全称)=1 then ''-合计-'' else b.公司全称 end'
+@s+',合计=sum(金额)
from opreport a inner join opcompany b on a.代理商=b.编号
group by a.代理商,b.公司全称 with rollup
having grouping(b.公司全称)=0 or grouping(a.代理商)=1')
go可是最后出现
服务器: 消息 1004,级别 15,状态 1,行 2
列前缀 '' 无效: 未指定表名
的错误 是怎么回事啊~
create table optype(编号 int,名称 varchar(10))
insert into optype
select 1,'test1'
union all select 2,'test2'
union all select 3,'test3'create table opreport(编号 int,业务种类 int,公司 int,金额 int,日期 datetime)
insert into opreport
select 1,1,1,20,'2003-10-12'
union all select 2,1,1,30,'2003-10-15'
union all select 3,2,2,32,'2003-10-15'
union all select 4,2,3,33,'2003-10-15'
union all select 3,1,2,52,'2003-10-15'
union all select 4,3,3,63,'2003-10-15'create table opcompany(编号 int,公司全称 varchar(10))
insert into opcompany
select 1,'A公司'
union all select 2,'B公司'
union all select 3,'C公司'
union all select 4,'D公司'
union all select 5,'E公司'--数据处理
declare @s varchar(8000)
set @s=''
select @s=@s+','+名称+'=sum(case 业务种类 when '
+cast(编号 as varchar)+' then 金额 else 0 end)'
from optypeexec('select 编号=a.公司
,公司名称=case when grouping(b.公司全称)=1 then ''-合计-'' else b.公司全称 end'
+@s+',合计=sum(金额)
from opreport a inner join opcompany b on a.公司=b.编号
group by a.公司,b.公司全称 with rollup
having grouping(b.公司全称)=0 or grouping(a.公司)=1')
godrop table optype,opreport,opcompany