条件都是相似的,其中的第一个条件:(a+b)v(a+c)目的1: 从mb0、mb1......mb100中导出e、f、g三个字段插入到mbd1中。 已知条件:mb0、mb1......mb100中各有一条记录的e、f、g字段与zb对应相等,称其为m记录,并另有一条记录的e、f、g字段与qb对应相等,称其为q记录,这个条件是肯定存在的。其他记录称为n记录。 所需条件:1.m.a+m.b=q.a+q.b and m.a+m.c=q.a+q.c 2.m.a+m.b!=n.a+n.b and m.a+m.c!=n.a+n.c 目的2:从wy中导出e、f、g三个字段插入到mbd1中。 所需条件:上面第二个条件即m.a+m.b!=n.a+n.b and m.a+m.c!=n.a+n.c的记录不为空。两个目的综合起来:如果第一个目的为空,即没有满足第一个条件的记录,则第二个目的就不用实现,第二个目的必须在第一个目的不为空的情况下执行。不知这样说是否明白。
TRY: declare @mb int,@sql varchar(8000) select @mb=0,@sql=''--创建一个将101个表数据合并的视图 while @mb <=100 begin select @sql=@sql+' union all select *,'+rtrim(@mb)+' as tabSN from mb'+rtrim(@mb), @mb=@mb+1 endset @sql='create view v_temp as '+stuff(@sqlsub,1,11,'') exec(@sql) go ---------------------------(a+b)v(a+c) set @sql=' if exists(select 1 from (select a.* from v_temp a,zb b where a.e=b.e and a.f=b.f and a.g=b.g) m, (select a.* from v_temp a,qb b where a.e=b.e and a.f=b.f and a.g=b.g) q where m.tabSN=q.tabSN and m.a+m.b=q.a+q.b and m.a+m.c=q.a+q.c) insert into mbd1 select m.e,m.f,m.g from zb z,v_temp m,v_temp n where m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.a+n.c!=m.a+m.c) union all select w.e,w.f,w.g from wy w where exists(select 1 from zb z,v_temp m,v_temp n where m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.a+n.c!=m.a+m.c))'exec(@sql) ---------------------------(a+b)v(a+d) set @sql=' if exists(select 1 from (select a.* from v_temp a,zb b where a.e=b.e and a.f=b.f and a.g=b.g) m, (select a.* from v_temp a,qb b where a.e=b.e and a.f=b.f and a.g=b.g) q where m.tabSN=q.tabSN and m.a+m.b=q.a+q.b and m.a+m.d=q.a+q.d) insert into mbd1 select m.e,m.f,m.g from zb z,v_temp m,v_temp n where m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.a+n.d!=m.a+m.d) union all select w.e,w.f,w.g from wy w where exists(select 1 from zb z,v_temp m,v_temp n where m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.a+n.d!=m.a+m.d))'exec(@sql) --------------------------(a+b)v(b+c) set @sql=' if exists(select 1 from (select a.* from v_temp a,zb b where a.e=b.e and a.f=b.f and a.g=b.g) m, (select a.* from v_temp a,qb b where a.e=b.e and a.f=b.f and a.g=b.g) q where m.tabSN=q.tabSN and m.a+m.b=q.a+q.b and m.b+m.c=q.b+q.c) insert into mbd1 select m.e,m.f,m.g from zb z,v_temp m,v_temp n where m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.b+n.c!=m.b+m.c) union all select w.e,w.f,w.g from wy w where exists(select 1 from zb z,v_temp m,v_temp n where m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.b+n.c!=m.b+m.c))'exec(@sql) go--删除视图 drop view v_temp go 注:代码没有测试,仅供参考
declare @mb int,@sql varchar(8000) select @mb=0,@sql=''--创建一个将101个表数据合并的视图 while @mb <=100 begin select @sql=@sql+' union all select *,'+rtrim(@mb)+' as tabSN from mb'+rtrim(@mb), @mb=@mb+1 endset @sql='create view v_temp as '+stuff(@sql,1,11,'') exec(@sql)---------------------------(a+b)v(a+c) set @sql=' if exists(select 1 from (select a.* from v_temp a,zb b where a.e=b.e and a.f=b.f and a.g=b.g) m, (select a.* from v_temp a,qb b where a.e=b.e and a.f=b.f and a.g=b.g) q where m.tabSN=q.tabSN and m.a+m.b=q.a+q.b and m.a+m.c=q.a+q.c) insert into mbd1 select m.e,m.f,m.g from zb z,v_temp m,v_temp n where m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.a+n.c!=m.a+m.c) union all select w.e,w.f,w.g from wy w where exists(select 1 from zb z,v_temp m,v_temp n where m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.a+n.c!=m.a+m.c))'exec(@sql) ---------------------------(a+b)v(a+d) set @sql=' if exists(select 1 from (select a.* from v_temp a,zb b where a.e=b.e and a.f=b.f and a.g=b.g) m, (select a.* from v_temp a,qb b where a.e=b.e and a.f=b.f and a.g=b.g) q where m.tabSN=q.tabSN and m.a+m.b=q.a+q.b and m.a+m.d=q.a+q.d) insert into mbd1 select m.e,m.f,m.g from zb z,v_temp m,v_temp n where m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.a+n.d!=m.a+m.d) union all select w.e,w.f,w.g from wy w where exists(select 1 from zb z,v_temp m,v_temp n where m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.a+n.d!=m.a+m.d))'exec(@sql) --------------------------(a+b)v(b+c) set @sql=' if exists(select 1 from (select a.* from v_temp a,zb b where a.e=b.e and a.f=b.f and a.g=b.g) m, (select a.* from v_temp a,qb b where a.e=b.e and a.f=b.f and a.g=b.g) q where m.tabSN=q.tabSN and m.a+m.b=q.a+q.b and m.b+m.c=q.b+q.c) insert into mbd1 select m.e,m.f,m.g from zb z,v_temp m,v_temp n where m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.b+n.c!=m.b+m.c) union all select w.e,w.f,w.g from wy w where exists(select 1 from zb z,v_temp m,v_temp n where m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.b+n.c!=m.b+m.c))'exec(@sql) --删除视图 drop view v_temp go
条件都是相似的,其中的第一个条件:(a+b)v(a+c)目的1:
从mb0、mb1......mb100中导出e、f、g三个字段插入到mbd1中。
已知条件:mb0、mb1......mb100中各有一条记录的e、f、g字段与zb对应相等,称其为m记录,并另有一条记录的e、f、g字段与qb对应相等,称其为q记录,这个条件是肯定存在的。其他记录称为n记录。
所需条件:1.m.a+m.b=q.a+q.b and m.a+m.c=q.a+q.c
2.m.a+m.b!=n.a+n.b and m.a+m.c!=n.a+n.c
目的2:从wy中导出e、f、g三个字段插入到mbd1中。
所需条件:上面第二个条件即m.a+m.b!=n.a+n.b and m.a+m.c!=n.a+n.c的记录不为空。两个目的综合起来:如果第一个目的为空,即没有满足第一个条件的记录,则第二个目的就不用实现,第二个目的必须在第一个目的不为空的情况下执行。不知这样说是否明白。
select @mb=0,@sql=''--创建一个将101个表数据合并的视图
while @mb <=100
begin
select @sql=@sql+' union all select *,'+rtrim(@mb)+' as tabSN from mb'+rtrim(@mb),
@mb=@mb+1
endset @sql='create view v_temp as '+stuff(@sqlsub,1,11,'')
exec(@sql)
go
---------------------------(a+b)v(a+c)
set @sql='
if exists(select
1
from
(select a.* from v_temp a,zb b where a.e=b.e and a.f=b.f and a.g=b.g) m,
(select a.* from v_temp a,qb b where a.e=b.e and a.f=b.f and a.g=b.g) q
where
m.tabSN=q.tabSN and m.a+m.b=q.a+q.b and m.a+m.c=q.a+q.c)
insert into mbd1
select
m.e,m.f,m.g
from
zb z,v_temp m,v_temp n
where
m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.a+n.c!=m.a+m.c)
union all
select
w.e,w.f,w.g
from
wy w
where
exists(select
1
from
zb z,v_temp m,v_temp n
where
m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.a+n.c!=m.a+m.c))'exec(@sql)
---------------------------(a+b)v(a+d)
set @sql='
if exists(select
1
from
(select a.* from v_temp a,zb b where a.e=b.e and a.f=b.f and a.g=b.g) m,
(select a.* from v_temp a,qb b where a.e=b.e and a.f=b.f and a.g=b.g) q
where
m.tabSN=q.tabSN and m.a+m.b=q.a+q.b and m.a+m.d=q.a+q.d)
insert into mbd1
select
m.e,m.f,m.g
from
zb z,v_temp m,v_temp n
where
m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.a+n.d!=m.a+m.d)
union all
select
w.e,w.f,w.g
from
wy w
where
exists(select
1
from
zb z,v_temp m,v_temp n
where
m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.a+n.d!=m.a+m.d))'exec(@sql)
--------------------------(a+b)v(b+c)
set @sql='
if exists(select
1
from
(select a.* from v_temp a,zb b where a.e=b.e and a.f=b.f and a.g=b.g) m,
(select a.* from v_temp a,qb b where a.e=b.e and a.f=b.f and a.g=b.g) q
where
m.tabSN=q.tabSN and m.a+m.b=q.a+q.b and m.b+m.c=q.b+q.c) insert into mbd1
select
m.e,m.f,m.g
from
zb z,v_temp m,v_temp n
where
m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.b+n.c!=m.b+m.c)
union all
select
w.e,w.f,w.g
from
wy w
where
exists(select
1
from
zb z,v_temp m,v_temp n
where
m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.b+n.c!=m.b+m.c))'exec(@sql)
go--删除视图
drop view v_temp
go
注:代码没有测试,仅供参考
select @mb=0,@sql=''--创建一个将101个表数据合并的视图
while @mb <=100
begin
select @sql=@sql+' union all select *,'+rtrim(@mb)+' as tabSN from mb'+rtrim(@mb),
@mb=@mb+1
endset @sql='create view v_temp as '+stuff(@sql,1,11,'')
exec(@sql)---------------------------(a+b)v(a+c)
set @sql='
if exists(select
1
from
(select a.* from v_temp a,zb b where a.e=b.e and a.f=b.f and a.g=b.g) m,
(select a.* from v_temp a,qb b where a.e=b.e and a.f=b.f and a.g=b.g) q
where
m.tabSN=q.tabSN and m.a+m.b=q.a+q.b and m.a+m.c=q.a+q.c)
insert into mbd1
select
m.e,m.f,m.g
from
zb z,v_temp m,v_temp n
where
m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.a+n.c!=m.a+m.c)
union all
select
w.e,w.f,w.g
from
wy w
where
exists(select
1
from
zb z,v_temp m,v_temp n
where
m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.a+n.c!=m.a+m.c))'exec(@sql)
---------------------------(a+b)v(a+d)
set @sql='
if exists(select
1
from
(select a.* from v_temp a,zb b where a.e=b.e and a.f=b.f and a.g=b.g) m,
(select a.* from v_temp a,qb b where a.e=b.e and a.f=b.f and a.g=b.g) q
where
m.tabSN=q.tabSN and m.a+m.b=q.a+q.b and m.a+m.d=q.a+q.d)
insert into mbd1
select
m.e,m.f,m.g
from
zb z,v_temp m,v_temp n
where
m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.a+n.d!=m.a+m.d)
union all
select
w.e,w.f,w.g
from
wy w
where
exists(select
1
from
zb z,v_temp m,v_temp n
where
m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.a+n.d!=m.a+m.d))'exec(@sql)
--------------------------(a+b)v(b+c)
set @sql='
if exists(select
1
from
(select a.* from v_temp a,zb b where a.e=b.e and a.f=b.f and a.g=b.g) m,
(select a.* from v_temp a,qb b where a.e=b.e and a.f=b.f and a.g=b.g) q
where
m.tabSN=q.tabSN and m.a+m.b=q.a+q.b and m.b+m.c=q.b+q.c) insert into mbd1
select
m.e,m.f,m.g
from
zb z,v_temp m,v_temp n
where
m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.b+n.c!=m.b+m.c)
union all
select
w.e,w.f,w.g
from
wy w
where
exists(select
1
from
zb z,v_temp m,v_temp n
where
m.tabSN=n.tabSN and (z.e=m.e and z.f=m.f and z.g=m.g) and (n.a+n.b!=m.a+m.b and n.b+n.c!=m.b+m.c))'exec(@sql)
--删除视图
drop view v_temp
go