sql 2000 两个表的数据对比，显示出多余的值！
请问如何通过SQL语句进行数据对比，用 chanyurenid 对比 userinfo_id ， 显示出 表二 中多余的值，也就是说让 userinfo_id=420 显示出来。
表一 ：hr_Training_records
id name chanyurenid
263 培训 421,422,423
表二：hr_Training_records_Record
id userinfo_id Training_id
1 420 263
2 421 263
3 422 263
4 423 263
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-23 16:22:29
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[hr_Training_records]
if object_id('[hr_Training_records]') is not null drop table [hr_Training_records]
go
create table [hr_Training_records]([id] int,[name] nvarchar(4),[chanyurenid] nvarchar(22))
insert [hr_Training_records]
select 263,N'培训','421,422,423'
--> 测试数据[hr_Training_records_Record]
if object_id('[hr_Training_records_Record]') is not null drop table [hr_Training_records_Record]
go
create table [hr_Training_records_Record]([id] int,[userinfo_id] int,[Training_id] int)
insert [hr_Training_records_Record]
select 1,420,263 union all
select 2,421,263 union all
select 3,422,263 union all
select 4,423,263
--------------生成数据--------------------------
select *
from [hr_Training_records_Record] a
WHERE NOT EXISTS (SELECT 1 FROM (
select
id,
a.name,
SUBSTRING([chanyurenid],number,CHARINDEX(',',[chanyurenid]+',',number)-number) as [chanyurenid]
from
[hr_Training_records] a,master..spt_values
where
number >=1 and number<=len([chanyurenid])
and type='p'
and substring(','+[chanyurenid],number,1)=',')b WHERE a.userinfo_id =b.chanyurenid)
----------------结果----------------------------
/*
id userinfo_id Training_id
----------- ----------- -----------
1 420 263
*/
------解决方案--------------------
select a.id,a.name,substring(a.chanyurenid,b.number,charindex(',',a.chanyurenid+',',b.number)-b.number) 'chanyurenid'
from hr_Training_records a,master.dbo.spt_values b
where a.id=263 --> 条件1: hr_Training_records.id=263
and b.type='P' and b.number between 1 and len(a.chanyurenid)
and substring(','+a.chanyurenid,b.number,1)=','
and substring(a.chanyurenid,b.number,charindex(',',a.chanyurenid+',',b.number)-b.number) not in
(select rtrim(userinfo_id)
from hr_Training_records_Record
where Training_id=263 --> 条件2: hr_Training_records_Record.Training_id=263
)
/*
id name chanyurenid
----------- ---------- --------------------
263 培训 424
(1 row(s) affected)
*/