--创建存储过程
create procedure prd_test1(
@qydm varchar(10) ,
@jcnr varchar(100))
as
begin
declare @s varchar(8000)
set @s = ''
set @s = 'select TB1_id,TB_name into temp from 表1 where TB1_id in('+@jcnr+')'
exec(@s)
set @s = ''
select @s = @s + ','+a.TB_name+b.TB0_name+' = sum(case when b.tb1_id = '+rtrim(a.TB1_id)+' and b.TB0_id = ' + rtrim(b.TB0_id) + ' then 1 else 0 end) '
from temp a,表0 b order by a.TB1_id,b.TB0_id
drop table temp set @s = 'select 单位代码 = isnull(a.qydm,''合计'')' + @s + ' from 表2 a left join 表3 b on a.qydm = b.qydm '
if(@qydm != '')
set @s = @s + ' where a.qydm = '''+@qydm+''''
set @s = @s + 'group by a.qydm with rollup'
print @s
exec(@s)
end--执行存储过程
exec prd_test1 '1-0001','1,2,3,4,5,6'
create procedure prd_test1(
@qydm varchar(10) ,
@jcnr varchar(100))
as
begin
declare @s varchar(8000)
set @s = ''
set @s = 'select TB1_id,TB_name into temp from 表1 where TB1_id in('+@jcnr+')'
exec(@s)
set @s = ''
select @s = @s + ','+a.TB_name+b.TB0_name+' = sum(case when b.tb1_id = '+rtrim(a.TB1_id)+' and b.TB0_id = ' + rtrim(b.TB0_id) + ' then 1 else 0 end) '
from temp a,表0 b order by a.TB1_id,b.TB0_id
drop table temp set @s = 'select 单位代码 = isnull(a.qydm,''合计'')' + @s + ' from 表2 a left join 表3 b on a.qydm = b.qydm '
if(@qydm != '')
set @s = @s + ' where a.qydm = '''+@qydm+''''
set @s = @s + 'group by a.qydm with rollup'
print @s
exec(@s)
end--执行存储过程
exec prd_test1 '1-0001','1,2,3,4,5,6'
-------------------------------------------------------------------------------
create table 表0(
TB0_id int,
TB0_name varchar(20)
)
insert into 表0 select 1,'一般'
insert into 表0 select 2,'好的'
insert into 表0 select 3,'差'create table 表1(
TB1_id int,
TB_name varchar(20),
tb_type int,
TB_zf int)
insert into 表1 select 1,'检查名称1',2,10
insert into 表1 select 2,'检查名称2',2,20
insert into 表1 select 3,'检查名称3',2,10
insert into 表1 select 4,'检查名称4',2,18
insert into 表1 select 5,'检查名称5',2,10
insert into 表1 select 6,'检查名称6',2,20create table 表2(
tb2_id int,
qydm varchar(20),
tb2_date datetime) insert into 表2 select 1,'1-0001',cast('2005-01-28' as datetime)
insert into 表2 select 2,'1-0003',cast('2005-01-28' as datetime)
insert into 表2 select 3,'1-0004',cast('2005-01-28' as datetime)
insert into 表2 select 4,'1-0005',cast('2005-01-28' as datetime)
insert into 表2 select 5,'1-0006',cast('2005-01-28' as datetime)
insert into 表2 select 6,'1-0007',cast('2005-01-28' as datetime)
insert into 表2 select 7,'1-0008',cast('2005-01-28' as datetime)create table 表3(
tb3_id int,
tb1_id int,
tb2_id int,
qydm varchar(20),
tb_type int,
TB_fz int,
TB0_id int )insert into 表3 select 1 ,1,1,'1-0001',2,10,1
insert into 表3 select 2 ,2,1,'1-0001',2,14,2
insert into 表3 select 3 ,3,1,'1-0001',2,4,3
insert into 表3 select 4 ,4,1,'1-0001',2,10,1
insert into 表3 select 5 ,5,1,'1-0001',2,8,2
insert into 表3 select 6 ,6,1,'1-0001',2,12,1
insert into 表3 select 7 ,1,2,'1-0003',2,8,3
insert into 表3 select 8 ,2,2,'1-0003',2,18,1
insert into 表3 select 9 ,3,2,'1-0003',2,8,2
insert into 表3 select 10,4,2,'1-0003',2,13,1
insert into 表3 select 12,5,2,'1-0003',2,5,3
insert into 表3 select 13,6,2,'1-0003',2,20,1
create procedure prd_test1(
@qydm varchar(10) ,
@jcnr varchar(100))
as
begin
declare @s varchar(8000)
set @s = ''
set @s = 'select TB1_id,TB_name into temp from 表1 where TB1_id in('+@jcnr+')'
exec(@s)
set @s = ''
select @s = @s + ','+a.TB_name+b.TB0_name+' = sum(case when b.tb1_id = '+rtrim(a.TB1_id)+' and b.TB0_id = ' + rtrim(b.TB0_id) + ' then 1 else 0 end) '
from temp a,表0 b order by a.TB1_id,b.TB0_id
drop table temp set @s = 'select 单位代码 = isnull(a.qydm,''合计'')' + @s + ' from 表2 a left join 表3 b on a.qydm = b.qydm '
if(@qydm != '')
set @s = @s + ' where a.qydm = '''+@qydm+''''
set @s = @s + 'group by a.qydm with rollup'
print @s
exec(@s)
end--执行存储过程
exec prd_test1 '1-0001','1,2,3,4,5,6'
drop procedure prd_test1
drop table 表0
drop table 表1
drop table 表2
drop table 表3