--计划表
create table 计划(序号 varchar(2),试验内容1 varchar(10),试验次数1 int,试验内容2 varchar(10),试验次数2 int,试验内容3 varchar(10),试验次数3 int,试验内容4 varchar(10),试验次数4 int,试验内容5 varchar(10),试验次数5 int,试验内容6 varchar(10),试验次数6 int)
insert 计划 select '1','抗拉强度',3,'延伸率',2,'',0,'',0,'',0,'',0
union all select '2','抗拉强度',2,'延伸率',3,'屈服强度',2,'',0,'',0,'',0
union all select '3','延伸率',3,'屈服强度',3,'',0,'',0,'',0,'',0
--试验数据表
create table 数据(序号 varchar(2),抗拉强度1 int,抗拉强度2 int,抗拉强度3 int,延伸率1 int,延伸率2 int,延伸率3 int,屈服强度1 int,屈服强度2 int,屈服强度3 int)
insert 数据 select '1',300,310,305,20,30,0,0,0,0
union all select '2',300,290,0,20,25,23,800,802,0
union all select '3',0,0,0,23,22,21,800,805,810
go
--创建处理的辅助函数
create function f_sql(
@试验内容1 varchar(10),@试验次数1 int,
@试验内容2 varchar(10),@试验次数2 int,
@试验内容3 varchar(10),@试验次数3 int,
@试验内容4 varchar(10),@试验次数4 int,
@试验内容5 varchar(10),@试验次数5 int,
@试验内容6 varchar(10),@试验次数6 int,
@fieldcount int
)returns varchar(8000)
as
begin
declare @re varchar(8000),@i int,@j int
select @re='',@i=1,@j=1
while @j<=@试验次数1 and @i<=@fieldcount
select @re=@re+',[字段'+cast(@i as varchar)+']='''
+left(@试验内容1,2)+'''+cast(['
+@试验内容1+cast(@j as varchar)+'] as varchar)'
,@i=@i+1,@j=@j+1
set @j=1
while @j<=@试验次数2 and @i<=@fieldcount
select @re=@re+',[字段'+cast(@i as varchar)+']='''
+left(@试验内容2,2)+'''+cast(['
+@试验内容2+cast(@j as varchar)+'] as varchar)'
,@i=@i+1,@j=@j+1
set @j=1
while @j<=@试验次数3 and @i<=@fieldcount
select @re=@re+',[字段'+cast(@i as varchar)+']='''
+left(@试验内容3,2)+'''+cast(['
+@试验内容3+cast(@j as varchar)+'] as varchar)'
,@i=@i+1,@j=@j+1
set @j=1
while @j<=@试验次数4 and @i<=@fieldcount
select @re=@re+',[字段'+cast(@i as varchar)+']='''
+left(@试验内容4,2)+'''+cast(['
+@试验内容4+cast(@j as varchar)+'] as varchar)'
,@i=@i+1,@j=@j+1
set @j=1
while @j<=@试验次数5 and @i<=@fieldcount
select @re=@re+',[字段'+cast(@i as varchar)+']='''
+left(@试验内容5,2)+'''+cast(['
+@试验内容5+cast(@j as varchar)+'] as varchar)'
,@i=@i+1,@j=@j+1
set @j=1
while @j<=@试验次数6 and @i<=@fieldcount
select @re=@re+',[字段'+cast(@i as varchar)+']='''
+left(@试验内容6,2)+'''+cast(['
+@试验内容6+cast(@j as varchar)+'] as varchar)'
,@i=@i+1,@j=@j+1
while @i<=@fieldcount
select @re=@re+',[字段'+cast(@i as varchar)+']='''''
,@i=@i+1
return(@re)
end
go
--调用函数生成处理的SQL语句
declare @s varchar(8000)
set @s=''
select @s=@s+'union all select 序号'
+dbo.f_sql(试验内容1,试验次数1,试验内容2,试验次数2,试验内容3,试验次数3,试验内容4,试验次数4,试验内容5,试验次数5,试验内容6,试验次数6,9)
+' from 数据 where 序号='+序号
from 计划
set @s=stuff(@s,1,10,'')
exec(@s)
go
--调试表
create table 调度(试验内容 varchar(10),试验次数 int)
insert 调度 select '抗拉强度',3
union all select '延伸率',3
go
--则根据调度查试验内容的处理为:
--首先创建处理的辅助函数
create function f_sql(
@试验内容 varchar(10),
@试验次数 int
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
while @试验次数>0
select @r=',['+@试验内容+cast(@试验次数 as varchar)+']'+@r
,@试验次数=@试验次数-1
return(@r)
end
go
--调用处理函数生成查询的SQL语句
declare @s varchar(8000)
set @s=''
select @s=@s+dbo.f_sql(试验内容,试验次数)
from 调度
set @s='select '+stuff(@s,1,1,'')+' from 试验数据表'