日期:2014-05-18 浏览次数:20549 次
select col from a where not exists(select 1 from b where col=a.col) union all select col from b where not exists(select 1 from a where col=b.col)
------解决方案--------------------
--你这个是一列的数据吗?先拆分后,再使用group by having count(1) = 1
/* 标题:简单数据拆分(version 2.0) 作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 时间:2010-05-07 地点:重庆航天职业学院 描述: 有表tb, 如下: id value ----------- ----------- 1 aa,bb 2 aaa,bbb,ccc 欲按id,分拆value列, 分拆后结果如下: id value ----------- -------- 1 aa 1 bb 2 aaa 2 bbb 2 ccc */ --1. 旧的解决方法(sql server 2000) create table tb(id int,value varchar(30)) insert into tb values(1,'aa,bb') insert into tb values(2,'aaa,bbb,ccc') go --方法1.使用临时表完成 SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id) FROM tb A, # B WHERE SUBSTRING(',' + A.[value], B.id, 1) = ',' DROP TABLE # --方法2.如果数据量小,可不使用临时表 select a.id , value = substring(a.value , b.number , charindex(',' , a.value + ',' , b.number) - b.number) from tb a join master..spt_values b on b.type='p' and b.number between 1 and len(a.value) where substring(',' + a.value , b.number , 1) = ',' --2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30)) insert into tb values(1,'aa,bb') insert into tb values(2,'aaa,bbb,ccc') go --方法1.使用xml完成 SELECT A.id, B.value FROM ( SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb ) A OUTER APPLY ( SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v) ) B --方法2.使用CTE完成 ;with tt as (select id,[value]=cast(left([value],charindex(',',[value]+',')-1) as nvarchar(100)),Split=cast(stuff([value]+',',1,charindex(',',[value]+','),'') as nvarchar(100)) from tb union all select id,[value]=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from tt where split>'' ) select id,[value] from tt order by id option (MAXRECURSION 0) DROP TABLE tb /* id value ----------- ------------------------------ 1 aa 1 bb 2 aaa 2 bbb 2 ccc (5 行受影响) */
------解决方案--------------------
create table taba(id varchar(20)) create table tabb(id varchar(20)) insert into taba values('A,B,C') insert into tabb values('B,C') ;with t as (select substring(','+a.id,c.number+1,charindex(',',a.id+',',c.number)-c.number) id from taba a inner join master.dbo.spt_values c on c.[type]='P' and substring(','+a.id,c.number,1)=',' ) select id from t where charindex(t.id,(select top 1 id from tabb))=0 id --------------------- A (1 row(s) affected)