/*测试环境
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