22,301
社区成员




create table personal
(
pid int,
name varchar(5)
)
create table meeting
(
bid int,
pidlist varchar(20)
)
insert personal
select 1,'aaa' union
select 2,'bbb' union
select 3,'ccc' union
select 4,'ddd' union
select 5,'eee' union
select 6,'fff' union
select 7,'ggg'
insert meeting
select 1,'|2|3|4|' union
select 2,'|1|3|4|' union
select 3,'|1|2|4|'
SELECT * FROM personal p WHERE NOT EXISTS(SELECT 1 FROM meeting WHERE CHARINDEX(CAST(p.pid AS VARCHAR(20)),pidlist)>0)
另外建议不要这样保存pidlist,这样不仅无法使用索引,对于更的需求会造成更大的不变。 使用多对多的关系保存人员和会议,这种pidlist只需要在做报表时呈现就可以了。
create table personal
(
pid int,
name varchar(5)
)
create table meeting
(
bid int,
pidlist varchar(20)
)
insert personal
select 1,'aaa' union
select 2,'bbb' union
select 3,'ccc' union
select 4,'ddd' union
select 5,'eee' union
select 6,'fff' union
select 7,'ggg'
insert meeting
select 1,'|2|3|4|' union
select 2,'|1|3|4|' union
select 3,'|1|2|4|'
select * from meeting as a ,personal as b
where a.pidlist not like '%|'+cast(b.pid as varchar)+'|%'
使用charindex()也可以實現