多表查询统计

[nandorin]

存在如下一系列表：
TABLE_200601,TABLE_200602,TABLE_200603......
规律：每月生成一个相同结构的表，前缀TABLE_＋年月为新的表名。

TABLE_200601表结构：
sBegin datetime,sEnd datetime,sName varchar(20),sDept varchar(20),sMemo varchar(20)


--单表的话比较简单
select sName,count(*) as vcCountNumber from TABLE_200601 group by sName
select sDept,count(*) as vcCountNumber from TABLE_200601 group by sDept

--多表的话如何写成一个存储过程来执行，实现统计的功能，求教～

我需要建立一个存储过程 pro_statbytype @sBegin @sEnd @sType
exec pro_statbytype '2006-1-1 00:00:00.000','2006-5-1 00:00:00.000','sName'

[microsofttyc]

不会，帮顶

[pj9413837]

CREATE PROCEDURE pro_statbytype
@sBegin datetime
@sEnd datetime
@sType varchar(20)
as
begin
SET NOCOUNT ON;
declare @l int
declare @n int
declare @sql_str varchar(1000)
set @n=1
set datediff(month,@sBegin,@sEnd)+1
set @sql_str=''
while @n<=@l
begin
if @n=1
begin
set @sql_str='select (select count(*) from TABLE_'+replace(convert(varchar(7),dateadd(month,@n,@sBegin),120),'-','')+' where sName='''+@sType+''')'
end
else
begin
set @sql_str='+(select count(*) from TABLE_'+replace(convert(varchar(7),dateadd(month,@n,@sBegin),120),'-','')+' where sName='''+@sType+''')'
end
set @n=n+1
end
'print @sql_str
exec(@sql_str)
end

[pj9413837]

我没测试，应该没什么问题，你先试试，有问题再发帖子

[nandorin]

好，谢谢，我先测试下。

[nandorin]

set datediff(month,@sBegin,@sEnd)+1
这里语法错误

[pj9413837]

set @l=datediff(month,@sBegin,@sEnd)+1

[pj9413837]

CREATE PROCEDURE pro_statbytype 
@sBegin datetime 
@sEnd datetime 
@sType varchar(20) 
as 
begin 
SET NOCOUNT ON; 
declare @l int 
declare @n int 
declare @sql_str varchar(1000) 
set @n=1 
set @ldatediff(month,@sBegin,@sEnd)+1 
set @sql_str='' 
while @n <=@l 
begin 
if @n=1 
begin 
set @sql_str='select (select count(*) from TABLE_'+replace(convert(varchar(7),dateadd(month,@n,@sBegin),120),'-','')+' where sName='''+@sType+''')' 
end 
else 
begin 
set @sql_str='+(select count(*) from TABLE_'+replace(convert(varchar(7),dateadd(month,@n,@sBegin),120),'-','')+' where sName='''+@sType+''')' 
end 
set @n=n+1 
end 
'print @sql_str 
        exec(@sql_str) 
end

[pj9413837]

CREATE PROCEDURE pro_statbytype 
@sBegin datetime,
@sEnd datetime,
@sType varchar(20) 
as 
begin 
	SET NOCOUNT ON; 
	declare @l int 
	declare @n int 
	declare @sql_str varchar(1000) 
	set @n=1 
	set @l=datediff(month,@sBegin,@sEnd)+1 
	set @sql_str='' 
	while @n <=@l 
	begin 
		if @n=1 
		begin 
			set @sql_str='select (select count(*) from TABLE_'+replace(convert(varchar(7),dateadd(month,@n,@sBegin),120),'-','')+' where sName='''+@sType+''')' 
		end 
		else 
		begin 
			set @sql_str='+(select count(*) from TABLE_'+replace(convert(varchar(7),dateadd(month,@n,@sBegin),120),'-','')+' where sName='''+@sType+''')' 
		end 
		set @n=n+1 
	end 
	--print @sql_str 
	exec(@sql_str) 
end

这次发的检查了语法，没语法报错了！
其他的你得自己测试了~~~