咨询：
有表Acode name zt1 N1 ,0,2 N2 ,0,4,3 N3 ,0,4,4 N4 ,0,5 N5 ,0,4,6 N6 ,0,...表Bdm mc001 M1002 M2003 M3004 M4005 M5...--需求TAB:--如表A含,4,就在表A数据列下方将表B的所有数据带到TAB,如不含,4,就不需要。--如结果TAB:DM_NEW MC_NEW1 N12 N2 001 M1 002 M2 003 M3 004 M4 005 M5 ...3 N3 001 M1 002 M2 003 M3 004 M4 005 M5 ...4 N45 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')goselect 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 allselect ltrim(a.code) , dm = '' , a.name , mc = '' , a.code px from a ) torder by px , case when code <> '' then 1 else 2 enddrop 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 M53 N3 001 N3 M1 002 N3 M2 003 N3 M3 004 N3 M4 005 N3 M54 N4 5 N5 001 N5 M1 002 N5 M2 003 N5 M3 004 N5 M4 005 N5 M56 N6 （所影响的行数为 21 行）*/
[解决办法]
declare @count int,@rn intdeclare @tb table (DM_NEW int,MC_NEW varchar(32))select @count =COUNT(1) from aset @rn =1while @count >0begin 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 +1end
[解决办法]
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')goselect ltrim(code) code,name,code as rid,0 as pid into tab from ainsert into tabselect b.*,code,1 as pidfrom a,bwhere charindex(',4,',','+a.zt+',')>0select code,name from tab order by rid,piddrop table a , b , tab/*************************code name------------ ----------1 N12 N2001 M1002 M2003 M3004 M4005 M53 N3001 M1002 M2003 M3004 M4005 M54 N45 N5001 M1002 M2003 M3004 M4005 M56 N6(21 行受影响)