在同一个表中，查找一个字段的值是否在另一个字段中能够找到，谢谢

[travissong]

[唐诗三百首]

create table table1(单号1 varchar(10),单号2 varchar(10))

insert into table1(单号1,单号2)
 select 'AA','EE' union all
 select 'BB','AA' union all
 select null,'GG' union all
 select 'DD','AA' union all
 select 'EE','BB' union all
 select 'FF',null


select 单号1=a.单号1+case when exists(select 1 from table1 b where b.单号2=a.单号1) 
                         then '(有)' else '' end,
       a.单号2
 into table2
 from table1 a

select 单号1,单号2 from table2

/*
单号1            单号2
-------------- ----------
AA(有)          EE
BB(有)          AA
NULL           GG
DD             AA
EE(有)          BB
FF             NULL

(6 row(s) affected)
*/

[二月十六]

--测试数据
if not object_id(N'table1') is null
	drop table table1
Go
Create table table1([单号1] nvarchar(22),[单号2] nvarchar(22))
if not object_id(N'table2') is null
	drop table table2
Go
Create table table2([单号1] nvarchar(22),[单号2] nvarchar(22))
Insert table1
select N'AA',N'EE' union all
select N'BB',N'AA' union all
select null,N'GG' union all
select N'DD',N'AA' union all
select N'EE',N'BB' union all
select N'FF',null
Go
--测试数据结束
INSERT INTO table2
SELECT 单号1 + CASE
                 WHEN EXISTS
                      (
                          SELECT * FROM dbo.table1 b WHERE a.单号1 = b.单号2
                      ) THEN
                     '(有)'
                 ELSE
                     ''
             END,
       a.单号2
FROM table1 a;
--读取测试结果
SELECT * FROM dbo.table2