用SQL语句嵌套可以做出来,但是那样比较复杂,而且性能非常差,
建议使用StoreProcedure实现:
create procedure sp_CreateTable
as
begin
if exists(select name from sysobjects where name='tableResult' and
type='U')
begin
drop table tableResult
end create table tableResult
(
Analyst_Name varchar(100),
Date1Count int,
Date2Count int,
TotalCount int,
Average numeric(10,2)
) declare cur_Name cursor for
select distinct Name from tableSrc open cur_Name declare @strName varchar(200),@Date12Count int,@Date13Count int,
@TotalCount int,@Average numeric(10,2) fetch next from cur_Name into @strName while (@@fetch_status=0)
begin
select @Date12Count=
(
select count(*) from tableSrc where
Analyst_Name=#strName
and year(send_date)*10000+month(send_date)*100+day(send_date) =20050812
)
select @Date13Count=
(
select count(*) from tableSrc where
Analyst_Name=@strName
and year(send_date)*10000+month(send_date)*100+day(send_date) =20050813
)
select @TotalCount=
(
select count(*) from tableSrc where Analyst_Name=@strName
) select @Average=cast(@TotalCount as float)/2.0
Analyst_Name varchar(100),
Date1Count int,
Date2Count int,
TotalCount int,
Average numeric(10,2) insert into tableResult(Analyst_Name,Date1Count,Date2Count,TotalCount,Average)
values(@strName,@Date12Count,@Date13Count,@TotalCount,@Average) fetch next from cur_Name into @strName
end
close cur_Name
deallocate cur_Nameend最后一行再根据tableResult统计出来即可
建议使用StoreProcedure实现:
create procedure sp_CreateTable
as
begin
if exists(select name from sysobjects where name='tableResult' and
type='U')
begin
drop table tableResult
end create table tableResult
(
Analyst_Name varchar(100),
Date1Count int,
Date2Count int,
TotalCount int,
Average numeric(10,2)
) declare cur_Name cursor for
select distinct Name from tableSrc open cur_Name declare @strName varchar(200),@Date12Count int,@Date13Count int,
@TotalCount int,@Average numeric(10,2) fetch next from cur_Name into @strName while (@@fetch_status=0)
begin
select @Date12Count=
(
select count(*) from tableSrc where
Analyst_Name=#strName
and year(send_date)*10000+month(send_date)*100+day(send_date) =20050812
)
select @Date13Count=
(
select count(*) from tableSrc where
Analyst_Name=@strName
and year(send_date)*10000+month(send_date)*100+day(send_date) =20050813
)
select @TotalCount=
(
select count(*) from tableSrc where Analyst_Name=@strName
) select @Average=cast(@TotalCount as float)/2.0
Analyst_Name varchar(100),
Date1Count int,
Date2Count int,
TotalCount int,
Average numeric(10,2) insert into tableResult(Analyst_Name,Date1Count,Date2Count,TotalCount,Average)
values(@strName,@Date12Count,@Date13Count,@TotalCount,@Average) fetch next from cur_Name into @strName
end
close cur_Name
deallocate cur_Nameend最后一行再根据tableResult统计出来即可
(
send_date varchar(10),
Analyst_Name varchar(10)
)
insert tb
select '2005-08-12','Zhang' union all
select '2005-08-12','Sun' union all
select '2005-08-12','Sun' union all
select '2005-08-13','Sun' union all
select '2005-08-13','Sun' union all
select '2005-08-13','Zhang' union all
select '2005-08-13','Zhang' --测试
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',['+send_date+']=sum(case when [send_date]='''+send_date+''' then 1.0 else 0.0 end)'
from tb group by send_date
set @sql='select [Analyst Name]=Analyst_Name'+@sql+','+
'[Total]=count(1)+0.0,'+
'[AVERAGE]=convert(decimal(12,1),(count(1)+0.0)/(select count(distinct send_date) from tb)) '+
' into ## '+
'from tb '+
'group by Analyst_Name with cube'+
' update ## set [Analyst Name]=''total'' where [Analyst Name] is null'
exec(@sql)
set @sql=''
select @sql=@sql+',avg(convert(decimal(12,2),['+name+']))' from tempdb..syscolumns where id=object_id('tempdb..##') and xtype<>167
set @sql='insert ## select ''AVERAGE'''+@sql+' from ## where [Analyst Name]<>''total'''+
' select * from ## order by case [Analyst Name] when ''total'' then 2 when ''AVERAGE'' then 3 else 1 end'+
' drop table ##'
--print @sql
exec(@sql)--删除测试环境
drop table tb--结果
/*
Analyst Name 2005-08-12 2005-08-13 Total AVERAGE
------------ ---------------------------------------- ---------------
Sun 2.0 2.0 4.0 2.0
Zhang 1.0 2.0 3.0 1.5
total 3.0 4.0 7.0 3.5
AVERAGE 1.5 2.0 3.5 1.8(所影响的行数为 4 行)
*/
建议使用StoreProcedure实现:
create procedure sp_CreateTable
as
begin
if exists(select name from sysobjects where name='tableResult' and
type='U')
begin
drop table tableResult
end create table tableResult
(
Analyst_Name varchar(100),
Date1Count int,
Date2Count int,
TotalCount int,
Average numeric(10,2)
) declare cur_Name cursor for
select distinct Name from tableSrc open cur_Name declare @strName varchar(200),@Date12Count int,@Date13Count int,
@TotalCount int,@Average numeric(10,2) fetch next from cur_Name into @strName while (@@fetch_status=0)
begin
select @Date12Count=
(
select count(*) from tableSrc where
Analyst_Name=#strName
and year(send_date)*10000+month(send_date)*100+day(send_date) =20050812
)
select @Date13Count=
(
select count(*) from tableSrc where
Analyst_Name=@strName
and year(send_date)*10000+month(send_date)*100+day(send_date) =20050813
)
select @TotalCount=
(
select count(*) from tableSrc where Analyst_Name=@strName
) select @Average=cast(@TotalCount as float)/2.0 insert into tableResult(Analyst_Name,Date1Count,Date2Count,TotalCount,Average)
values(@strName,@Date12Count,@Date13Count,@TotalCount,@Average) fetch next from cur_Name into @strName
end
close cur_Name
deallocate cur_Nameend最后一行再根据tableResult统计出来即可
go
insert into table1
select '2005-08-12','Zhang' union all
select '2005-08-12','Sun' union all
select '2005-08-12','Sun' union all
select '2005-08-13','Sun' union all
select '2005-08-13','Sun' union all
select '2005-08-13','Zhang' union all
select '2005-08-13','Zhang' select * from table1select distinct analyst_name,[2005-08-12]=(select count(1) from
table1 b where b.analyst_name=a.analyst_name and send_date='2005-08-12'),
[2005-08-13]=(select count(1) from table1 b where b.analyst_name=a.analyst_name
and send_date='2005-08-13'),total=(select count(1)from table1 b where b.analyst_name=a.analyst_name ),
average=(select count(1)from table1 b where b.analyst_name=a.analyst_name )/2.0
from table1 a--测试结果
/*
send_date analyst_name
--------------- --------------------
2005-08-12 Zhang
2005-08-12 Sun
2005-08-12 Sun
2005-08-13 Sun
2005-08-13 Sun
2005-08-13 Zhang
2005-08-13 Zhang(所影响的行数为 7 行)analyst_name 2005-08-12 2005-08-13 total average
-------------------- ----------- ----------- ----------- -------------------
Sun 2 2 4 2.000000
Zhang 1 2 3 1.500000(所影响的行数为 2 行)
*/--删除测试数据
drop table table1
insert into #t select '2005-08-12','Zhang'
insert into #t select '2005-08-12','Sun'
insert into #t select '2005-08-12','Sun'
insert into #t select '2005-08-13','Sun'
insert into #t select '2005-08-13','Sun'
insert into #t select '2005-08-13','Zhang'
insert into #t select '2005-08-13','Zhang'
declare @s varchar(8000),@s1 varchar(8000),@s2 varchar(8000),@i int
select @s1 = '',@s2 = '',@i = 0select
@s1 = @s1 + ',['+send_date+']=sum(case when send_date='''+send_date+''' then 1.0 else 0.0 end)',
@s2 = @s2 + ',['+send_date+']=sum(case when send_date='''+send_date+''' then 1.0 else 0.0 end)/(select count(distinct Analyst_Name) from #T)',
@i = @i + 1
from
(select distinct send_date=convert(char(10),send_date,120) from #t) aset @s = ' select Analyst_Name'+@s1+',TOTAL=cast(count(*) as numeric(5,2)),AVERAGE=cast(count(*)/'+rtrim(@i) +' as numeric(5,2)) from #t group by Analyst_Name'
+ ' union all'
+ ' select ''total'''+@s1+',TOTAL=cast(count(*) as numeric(5,2)),AVERAGE=cast(count(*)/'+rtrim(@i) +' as numeric(5,2)) from #t'
+ ' union all'
+ ' select ''AVERAGE'''+@s2+',TOTAL=cast((sum(1)+0.0)/(select count(distinct Analyst_Name) from #T) as numeric(5,2)),AVERAGE=cast(count(*)/((select count(distinct Analyst_Name)+0.0 from #T)*'+rtrim(@i)+') as numeric(5,2)) from #t'
print @s
exec(@s)