declare @s varchar(8000)
set @s=''
select @s=@s+mid from tableB
select @s=@s+mid+',' from tableA where ','+@s not like '%,'+mid+',%'
set @s=''
select @s=@s+mid from tableB
select @s=@s+mid+',' from tableA where ','+@s not like '%,'+mid+',%'
create table B(mid varchar(5) )
insert into A
select 1 union select 2 union select 4 union select 3
insert into B
select '1,2,' union select '5,8'
----------------------------------------------
------------------下面建過程來處理----Create Proc pkk
as
Create Table #b(mid int)
Declare @s varchar(20)
declare kk cursor for select mid from b
open kk
fetch next from kk into @s
while @@fetch_status=0
begin
while @s<>''
begin
if charindex(',',@s)>0
insert into #b select substring(@s,1,charindex(',',@s)-1)
else
begin
insert into #b select @s
break
end
set @s=substring(@s,charindex(',',@s)+1,len(@s))
end
fetch next from kk into @s
end
close kk
deallocate kk
Declare @p varchar(100)
set @p=''
select @p=@p+cast(mid as varchar(3))+',' from
(select * from #b union select * from a) d order by mid
set @p=left(@p,len(@p)-1)
select @p
drop table #b---------------------end proc--------
exec pkk -------執行
----------結果輸出-------------
1,2,3,4,5,8
declare @s varchar(8000)
create table tableA(mid varchar(10))
insert tableA
select '1' union all select '5' union all select '4' union all select '3'
create table tableB(mid varchar(10))
insert tableB
select '1,2,' union all select '5,8,'
set @s=''
select @s=@s+mid from tableB
select @s=@s+mid+',' from tableA where ','+@s not like '%,'+mid+',%'
print @s
table A tableB
mid mid
1 1,2,
5 5,8,
4 2,5,
3
这样,是不是不好办了。
mid mid
1 1,2,
5 5,8,
4
3--=====================
--create function func_Split
CREATE FUNCTION func_Split
(@s varchar(2000))RETURNS @tb TABLE
(value nvarchar(100))
AS
BEGIN
declare @s1 varchar(20) --可以根据字段长度修改vachar的长度值
if right(@s,1) <> ',' --如果不是以','结尾,则增加一个',',增加循环个数
select @s=@s+ ','
while PATINDEX('%,%', @s) > 0
begin
select @s1 = left(@s,PATINDEX('%,%', @s)-1)
select @s = substring(@s,PATINDEX('%,%', @s)+1,len(@s))
INSERT INTO @tb(value) values (@s1)
end
RETURN
END
--begin create procedure
create procedure getstr AS
declare @B nvarchar(3000)
declare @sqlstr nvarchar(3000)--create temp table
select INTO #temp from A
select @B=@B+ cast(mid AS varchar(10)) from table B--output
print @B--input temp table from @B and call FUNCTION func_Split
insert into #temp select [value] from func_Split(@B) where [value] not in (select mid from #temp)--output
select distinct mid from #temp--drop #temp
drop table #temp--end procedure
INSERT INTO [c]([min])
SELECT [min] FROM [b] WHERE ([min] NOT IN (SELECT [MIN] FROM [a]))
select * from c
select [min] into [c] from [a]
INSERT INTO [c]([min])
SELECT [min] FROM [b] WHERE ([min] NOT IN (SELECT [MIN] FROM [a]))
select * from c
已经测试过可行
declare @s varchar(8000)
set @s=''
select @s=@s+CONVERT(varchar(3),[min])+',' from [a]
select @s=@s+CONVERT(varchar(3),[min])+',' from [b] where ([min] not in (select min] from [a]))
select @s
我的实现是先将tableB中的数据distinct再@s=@s+mid最后根据风云的方法,实现的。