sql统计之存储过程

/*测试环境
create table #表1
(
  性别 int,
  政治面貌 int
)
create table #表2
(
  id int,
  info varchar(10)
)
create table #表3
(
  id int,
  info varchar(10)
)
insert #表3
select 1     ,'男' union
select 2     ,'女'

insert #表2
select 0     ,'__' union
select 1     ,'群众' union
select 2     ,'共产' union
select 3     ,'共青'

insert #表1
select 1,                  1 union
select 2,                  3 union
select 2,                  0 union
select 1,                 2 union
select 2,                  1
调用
exec p 政治面貌,性别,表1,表2,表3*/
IF EXISTS (SELECT name
    FROM   sysobjects
    WHERE  name = 'p'
    AND    type = 'P')
    DROP PROCEDURE p
GO
create procedure p
@p1 varchar(50),
@p2 varchar(50),
@t1 varchar(50),
@t2 varchar(50),
@t3 varchar(50)
as
declare @sql varchar(8000)
set @sql='
declare @sql varchar(8000)
set @sql=''''
select @sql=@sql+'',sum(case when C.info=''''''+info+'''''' then 1 else 0 end) as ''''''+info+''''''''
from '+@t3+' group by info
select @sql=''select (case when grouping(B.info)=1 then ''''合计'''' else B.info end) '''''+@p1+'''''''+@sql
            +'',count(1) as ''''合计''''''
            +'' from '+@t1+' A join '+@t2+' B on A.'+@p1+'=B.id join '+@t3+' C on A.'+@p2+'=C.id''
            +'' group by B.info with rollup''
exec(@sql)'

exec(@sql)
go

 

结果：

政治面貌     男    女    合计
__           0     1       1
群众         1     1       2
共产     1     0       1
共青     0     1       1
合计         2     3       5