if exists(select * from sysobjects where name='sp_ha_analysisOneCol'
and xtype ='P')
drop proc sp_ha_analysisOneCol
goCreate proc sp_ha_analysisOneCol
( @tbName_in varchar(100), --待分析表(限一个表)
@cFixCols varchar(800), --固定列(可以多列)
@cAnalyCols varchar(100), --分析列(限一列)
@valCol varchar(100), --值列(限一列)
@bSum bit, --是否统计合计(1为统计,0为不统计)
@iPrecision int --值列小数位的精度
)
As
-- Author: BlueSky(or shuichangliu);
-- Function:
-- 调用示例:sp_ha_analysisOneCol 'tb_result','vcID,vcRe','vcCourse','fResult',1,1
--Release date: March 8th,2004;
set nocount on
----固定列不能为空
if LTrim(RTrim(@cFixCols))=''
begin
raiserror('You choose no fixed columns',15,1)
return -1
end
----检验临时表是否已经存在
if not(object_id('tempdb..##tmp_fix') is null)
begin
drop table ##tmp_fix
end
if not(object_id('tempdb..##tmp_analysis') is null)
begin
drop table ##tmp_analysis
end
----待分析表的数据插入临时表
declare @sTabIn varchar(50)
set @sTabIn='##tmp_'+convert(varchar(38),newid())
set @sTabIn=replace(@sTabIn,'-','')
exec(' select * into ['+@sTabIn + '] from ['+ @tbName_in+']')
----如果需要按固定列分组统计值列
if @bSum='1'
begin
exec(' insert into ['+ @sTabIn+']('+@cFixCols+','+@cAnalyCols+','+@valCol+')'+
' select '+@cFixCols+',''小计'' as 小计,sum(isnull('+@valCol+',0))'+
' from ['+@tbName_in+
'] group by '+ @cFixCols)
if @@error<>0
begin
goto errSum
end
end
----把固定列插入临时表
declare @str1 varchar(1000)
set @str1=' select distinct '+@cFixCols+
' into [##tmp_fix] from ['+@sTabIn+'] order by '+@cFixCols
exec(@str1)
if @@error<>0
begin
goto errSum
end
----把分系列插入临时表
set @str1=' select distinct '+@cAnalyCols+
' into [##tmp_analysis] from ['+@sTabIn +']'
exec(@str1)
if @@error<>0
begin
goto errSum
end ------------------获取固定列的列名--------------------;
declare @iIndex int,@iFieldLen int,@iFlag int
declare @cStrFixCols varchar(1000),@strTemp varchar(200)
declare @strFixedField varchar(4000)
set @iIndex=1
set @iFlag=1
set @strFixedField=''
set @cStrFixCols=RTrim(LTrim(@cFixCols))
while(charindex(',',@cStrFixCols,@iIndex)>0)
begin
set @iFieldLen=charindex(',',@cStrFixCols,@iIndex)-1 --field_char_len
set @strTemp=substring(@cStrFixCols,1,@iFieldLen)
if @iFlag=1
begin
set @strFixedField=@strFixedField + ' on x.'+@strTemp+' =z.'+ @strTemp
end
else
begin
set @strFixedField=@strFixedField + ' and x.'+@strTemp+' =z.'+ @strTemp
end
set @cStrFixCols=substring(@cStrFixCols,@iFieldLen+2,len(@cStrFixCols)-@iFieldLen-1)
set @iFlag=@iFlag+1
end
if @iFlag=1
begin
set @strFixedField=@strFixedField + ' on x.'+@cStrFixCols+' =z.'+ @cStrFixCols
end
else
begin
set @strFixedField=@strFixedField + ' and x.'+@cStrFixCols+' =z.'+ @cStrFixCols
end
--------------------分析数据--------------------;
declare @strSQL varchar(4000)
set @strSQL=
' declare @fieldName varchar(50) '+
' declare @strTmp varchar(2000) '+
' declare @sCol varchar(100) '+
' set @sCol=''|'+@valCol+''''+
' Declare curTest Cursor for ' +
' select distinct '+@cAnalyCols +' from ' +' [##tmp_analysis] '+
' open curTest '+
' fetch next from curTest into @fieldName' +
' while @@fetch_status=0 '+
' begin '+
' select @strTmp= '+
--''' alter table ##tmp_fix add '+'''+'+'@fieldName+'+ ''' decimal(16,4) '''+
''' alter table ##tmp_fix add ['+'''+'+'@fieldName+'+ '''] int '''+
' exec(@strTmp) '+
' select @strTmp= '+
''' update ##tmp_fix set [''+ @fieldName+'+''']=IsNull('+'z.'+@valCol+',0)'+'''+'+
''' from [##tmp_fix] x inner join ['+ @sTabIn +'] z '+@strFixedField+''' +'''+
' where z.' +@cAnalyCols+' ='+'''''''+@fieldName+'''''''+''''+
' print @strtmp'+
' exec(@strTmp) '+
--控制值列的精度(update date:2004-03-13)
--' select @strTmp='+'''update ##tmp_fix set ''+ @fieldName+''=isnull(''+@fieldName+'' ,0)'''+
--' exec(@strTmp) '+
--' select @strTmp='+'''update ##tmp_fix set ''+
--@fieldName+'' =cast(isnull(''+@fieldName+'',0) as decimal(14,''+'+''''+ltrim(str(@iPrecision))+''''+'+''))'''+
--' print @strtmp'+
--' exec(@strTmp) '+
' fetch next from curTest into @fieldName '+
' end '+
' close curTest'+
' deallocate curTest'
--Print @strSQL
exec(@strSQL)
if @@error<>0
begin
raiserror('Error!',15,1)
goto errSum
end
----清除临时表
exec(' select * from [##tmp_fix] order by '+ @cFixCols)
drop table ##tmp_analysis
drop table ##tmp_fix
exec(' drop table ['+@sTabIn+']')
return 0 ------Success
errSum:
exec(' drop table ['+@sTabIn+']')
return -1 ------Fail
----调用示例
create table tabName
( filename char(6),
jinguoriqi datetime )insert tabName
select 'aa528','2004-02-04'
union all select 'aa521','2004-03-01'
union all select 'bb213','2004-05-25'
union all select 'aa522','2004-03-01'
union all select 'bb214','2004-05-26'
union all select 'aa523','2004-03-05'
union all select 'cc213','2004-05-26'
select * from tabNameif not(object_id('tempdb..#tmpTest') is null)
drop table #tmpTest
go
select [fileName]=left(fileName,2) ,
[dt]=convert(varchar(10),jinguoriqi,120),
[sl]=count(convert(varchar(10),jinguoriqi,120))
into #tmpTest
from tabName
group by left(fileName,2),convert(varchar(10),jinguoriqi,120)
Go
sp_ha_analysisOneCol '#tmpTest','fileName','dt','sl','0',0
and xtype ='P')
drop proc sp_ha_analysisOneCol
goCreate proc sp_ha_analysisOneCol
( @tbName_in varchar(100), --待分析表(限一个表)
@cFixCols varchar(800), --固定列(可以多列)
@cAnalyCols varchar(100), --分析列(限一列)
@valCol varchar(100), --值列(限一列)
@bSum bit, --是否统计合计(1为统计,0为不统计)
@iPrecision int --值列小数位的精度
)
As
-- Author: BlueSky(or shuichangliu);
-- Function:
-- 调用示例:sp_ha_analysisOneCol 'tb_result','vcID,vcRe','vcCourse','fResult',1,1
--Release date: March 8th,2004;
set nocount on
----固定列不能为空
if LTrim(RTrim(@cFixCols))=''
begin
raiserror('You choose no fixed columns',15,1)
return -1
end
----检验临时表是否已经存在
if not(object_id('tempdb..##tmp_fix') is null)
begin
drop table ##tmp_fix
end
if not(object_id('tempdb..##tmp_analysis') is null)
begin
drop table ##tmp_analysis
end
----待分析表的数据插入临时表
declare @sTabIn varchar(50)
set @sTabIn='##tmp_'+convert(varchar(38),newid())
set @sTabIn=replace(@sTabIn,'-','')
exec(' select * into ['+@sTabIn + '] from ['+ @tbName_in+']')
----如果需要按固定列分组统计值列
if @bSum='1'
begin
exec(' insert into ['+ @sTabIn+']('+@cFixCols+','+@cAnalyCols+','+@valCol+')'+
' select '+@cFixCols+',''小计'' as 小计,sum(isnull('+@valCol+',0))'+
' from ['+@tbName_in+
'] group by '+ @cFixCols)
if @@error<>0
begin
goto errSum
end
end
----把固定列插入临时表
declare @str1 varchar(1000)
set @str1=' select distinct '+@cFixCols+
' into [##tmp_fix] from ['+@sTabIn+'] order by '+@cFixCols
exec(@str1)
if @@error<>0
begin
goto errSum
end
----把分系列插入临时表
set @str1=' select distinct '+@cAnalyCols+
' into [##tmp_analysis] from ['+@sTabIn +']'
exec(@str1)
if @@error<>0
begin
goto errSum
end ------------------获取固定列的列名--------------------;
declare @iIndex int,@iFieldLen int,@iFlag int
declare @cStrFixCols varchar(1000),@strTemp varchar(200)
declare @strFixedField varchar(4000)
set @iIndex=1
set @iFlag=1
set @strFixedField=''
set @cStrFixCols=RTrim(LTrim(@cFixCols))
while(charindex(',',@cStrFixCols,@iIndex)>0)
begin
set @iFieldLen=charindex(',',@cStrFixCols,@iIndex)-1 --field_char_len
set @strTemp=substring(@cStrFixCols,1,@iFieldLen)
if @iFlag=1
begin
set @strFixedField=@strFixedField + ' on x.'+@strTemp+' =z.'+ @strTemp
end
else
begin
set @strFixedField=@strFixedField + ' and x.'+@strTemp+' =z.'+ @strTemp
end
set @cStrFixCols=substring(@cStrFixCols,@iFieldLen+2,len(@cStrFixCols)-@iFieldLen-1)
set @iFlag=@iFlag+1
end
if @iFlag=1
begin
set @strFixedField=@strFixedField + ' on x.'+@cStrFixCols+' =z.'+ @cStrFixCols
end
else
begin
set @strFixedField=@strFixedField + ' and x.'+@cStrFixCols+' =z.'+ @cStrFixCols
end
--------------------分析数据--------------------;
declare @strSQL varchar(4000)
set @strSQL=
' declare @fieldName varchar(50) '+
' declare @strTmp varchar(2000) '+
' declare @sCol varchar(100) '+
' set @sCol=''|'+@valCol+''''+
' Declare curTest Cursor for ' +
' select distinct '+@cAnalyCols +' from ' +' [##tmp_analysis] '+
' open curTest '+
' fetch next from curTest into @fieldName' +
' while @@fetch_status=0 '+
' begin '+
' select @strTmp= '+
--''' alter table ##tmp_fix add '+'''+'+'@fieldName+'+ ''' decimal(16,4) '''+
''' alter table ##tmp_fix add ['+'''+'+'@fieldName+'+ '''] int '''+
' exec(@strTmp) '+
' select @strTmp= '+
''' update ##tmp_fix set [''+ @fieldName+'+''']=IsNull('+'z.'+@valCol+',0)'+'''+'+
''' from [##tmp_fix] x inner join ['+ @sTabIn +'] z '+@strFixedField+''' +'''+
' where z.' +@cAnalyCols+' ='+'''''''+@fieldName+'''''''+''''+
' print @strtmp'+
' exec(@strTmp) '+
--控制值列的精度(update date:2004-03-13)
--' select @strTmp='+'''update ##tmp_fix set ''+ @fieldName+''=isnull(''+@fieldName+'' ,0)'''+
--' exec(@strTmp) '+
--' select @strTmp='+'''update ##tmp_fix set ''+
--@fieldName+'' =cast(isnull(''+@fieldName+'',0) as decimal(14,''+'+''''+ltrim(str(@iPrecision))+''''+'+''))'''+
--' print @strtmp'+
--' exec(@strTmp) '+
' fetch next from curTest into @fieldName '+
' end '+
' close curTest'+
' deallocate curTest'
--Print @strSQL
exec(@strSQL)
if @@error<>0
begin
raiserror('Error!',15,1)
goto errSum
end
----清除临时表
exec(' select * from [##tmp_fix] order by '+ @cFixCols)
drop table ##tmp_analysis
drop table ##tmp_fix
exec(' drop table ['+@sTabIn+']')
return 0 ------Success
errSum:
exec(' drop table ['+@sTabIn+']')
return -1 ------Fail
----调用示例
create table tabName
( filename char(6),
jinguoriqi datetime )insert tabName
select 'aa528','2004-02-04'
union all select 'aa521','2004-03-01'
union all select 'bb213','2004-05-25'
union all select 'aa522','2004-03-01'
union all select 'bb214','2004-05-26'
union all select 'aa523','2004-03-05'
union all select 'cc213','2004-05-26'
select * from tabNameif not(object_id('tempdb..#tmpTest') is null)
drop table #tmpTest
go
select [fileName]=left(fileName,2) ,
[dt]=convert(varchar(10),jinguoriqi,120),
[sl]=count(convert(varchar(10),jinguoriqi,120))
into #tmpTest
from tabName
group by left(fileName,2),convert(varchar(10),jinguoriqi,120)
Go
sp_ha_analysisOneCol '#tmpTest','fileName','dt','sl','0',0
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)select distinct jinguoriqi into #tmp from tablenameset @sql=''set rowcount 100
select @sql=@sql+',sum(case jinguoriqi when '''+convert(char(10),jinguoriqi,120)+''' then 1 else 0 end) as ['+convert(char(10),jinguoriqi,120)+']'
from #Tmp as x order by jinguoriqidelete #tmpset rowcount 0
set @sql1=''
set @sql2=''if exists (select * from #tmp)
beginset rowcount 100
select @sql1=@sql1+',sum(case jinguoriqi when '''+convert(char(10),jinguoriqi,120)+''' then 1 else 0 end) as ['+convert(char(10),jinguoriqi,120)+']'
from #Tmp as x order by jinguoriqidelete #tmpset rowcount 0
endif exists (select * from #tmp)
beginset rowcount 100
select @sql2=@sql2+',sum(case jinguoriqi when '''+convert(char(10),jinguoriqi,120)+''' then 1 else 0 end) as ['+convert(char(10),jinguoriqi,120)+']'
from #Tmp as x order by jinguoriqidelete #tmpset rowcount 0
end---如果还不够,你自己再加set @sql='select left(filename,2) as [left(filename,2)]'+@sqlSET @sql2=@sql2+' from tablename group by left(filename,2)'
exec (@sql+@sql1+@sql2)
错了不管的
哈,哈哈,哈哈哈......