有表A
code      name        zt
1          N1         ,0,
2          N2         ,0,4,
3          N3         ,0,4,
4          N4         ,0,
5          N5         ,0,4,
6          N6         ,0,
...

表B
dm         mc
001        M1
002        M2
003        M3
004        M4
005        M5
...

--需求TAB:
--如表A含,4,就在表A数据列下方将表B的所有数据带到TAB,如不含,4,就不需要。
--如结果TAB:

DM_NEW           MC_NEW
1                  N1
2                  N2
  001                 M1
  002                 M2
  003                 M3
  004                 M4
  005                 M5
  ...
3                  N3
  001                 M1
  002                 M2
  003                 M3
  004                 M4
  005                 M5
  ...
4                  N4
5                  N5
  001                 M1
  002                 M2
  003                 M3
  004                 M4
  005                 M5
  ...
6                  N6
...

create table A(code int,     name varchar(10),       zt varchar(10))
insert into a values(1 ,         'N1',         ',0,')
insert into a values(2 ,         'N2',         ',0,4,')
insert into a values(3 ,         'N3',         ',0,4,')
insert into a values(4 ,         'N4',         ',0,')
insert into a values(5 ,         'N5',         ',0,4,')
insert into a values(6 ,         'N6',         ',0,')
create table b(dm varchar(10),        mc varchar(10))
insert into b values('001',        'M1')
insert into b values('002',        'M2')
insert into b values('003',        'M3')
insert into b values('004',        'M4')
insert into b values('005',        'M5')
go

select code , dm , name , mc from
(
select code = '', b.dm , a.name , b.mc , a.code px from a , b where charindex(',4,' , zt) > 0 
union all
select ltrim(a.code) , dm = '' , a.name , mc = '' , a.code px from a 
) t
order by px , case when code <> '' then 1 else 2 end


drop table a , b

/*
code         dm         name       mc         
------------ ---------- ---------- ---------- 
1                       N1         
2                       N2         
             001        N2         M1
             002        N2         M2
             003        N2         M3
             004        N2         M4
             005        N2         M5
3                       N3         
             001        N3         M1
             002        N3         M2
             003        N3         M3
             004        N3         M4
             005        N3         M5
4                       N4         
5                       N5         
             001        N5         M1
             002        N5         M2
             003        N5         M3
             004        N5         M4
             005        N5         M5
6                       N6         

（所影响的行数为 21 行）


*/

------解决方案--------------------

SQL code


declare @count int,@rn int
declare @tb table (DM_NEW int,MC_NEW varchar(32))
select @count =COUNT(1) from a
set @rn =1
while @count >0
begin
    insert into @tb select top(1) code,name from (select ROW_NUMBER() over(order by code )rn, code,name from a where rn=@rn)aa
    if exists (select 1 from a where zt like '%4%'
        begin
            insert into @tb select id,name from b
        end
    set @count=@count-1
    set @rn =@rn +1
end

------解决方案--------------------

SQL code

create table A(code int,     name varchar(10),       zt varchar(10))
insert into a values(1 ,         'N1',         ',0,')
insert into a values(2 ,