关于统计某张表中的多个字段
sqlserver 05中 某表T ，其中有字段id，creator，type，memo。其中type 存在类型ABC，现想按照creator 查询这个人每种type 录了多少记录以及总共录了多少条。人员大概两到三百，表不大，十余万的记录。
----------------------------------------------------------------------------
我想了一种实现方法：存储过程中建立临时表t1，然后用游标遍历creator，用creator 去分别查询出每个type的记录条数插入临时表，最后select 出来。。。这可行么，求教 查询sql?临时表 查询 临时表
[解决办法]
declare @sql varchar(8000)
set @sql=''
select @sql=@sql + ',['+rtrim(type)+']=sum(case type when '''+rtrim(type)+''' then 1 end)'
from tb group by type
exec ('select creator'+@sql+',count(*) as total from tb group by creator' )
create table 表T
(id int,creator varchar(10),type varchar(5),memo int)
insert into 表T
select 1,'lol','A',101 union all
select 2,'lol','B',102 union all
select 3,'lol','A',103 union all
select 4,'lol','A',104 union all
select 5,'lol','A',105 union all
select 6,'lol','B',106 union all
select 7,'lox','B',107 union all
select 8,'lox','A',108 union all
select 9,'lox','A',109 union all
select 10,'lox','A',110
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')
+'sum(case when [type]='''+[type]+''' then 1 else 0 end) ''type'+[type]+''' '
from (select distinct [type] from 表T) t
select @tsql='select creator,'+@tsql+',count(1) ''ALL'' '
+' from 表T group by creator '
exec(@tsql)
/*
creator typeA typeB ALL
---------- ----------- ----------- -----------
lol 4 2 6
lox 3 1 4
(2 row(s) affected)
*/
--> 测试数据：t
if object_id('t') is not null drop table t
go
create table t([ID] int,creator varchar(6),type varchar(6),memo varchar(6))
insert t
select 1,'JIM','A','ASD' union all
select 2,'JIM','A','QWE' union all
select 3,'KIMI','A','ASD' union all
select 4,'KIMI','B','ASD' union all
select 5,'KIMI','B','ASD'
SELECT creator,type,COUNT(*) AS t_COUNT into #t FROM T GROUP BY creator,type
declare @sql varchar(max)
set @sql=''
select @sql=@sql + ',['+rtrim(type)+']=sum(case type when '''+rtrim(type)+''' then t_COUNT else 0 end)'
from #t group by type
exec('select creator'+@sql+'from #t group by creator' )
drop table t
