create table #t1(老师 varchar(10),课程 varchar(10)) insert into #t1 select '李老师','1 2' insert into #t1 select '张老师',NULL insert into #t1 select '齐老师','1 3 2' create table #t2(课程号 int,课程名 varchar(10)) insert into #t2 select 1,'语文' insert into #t2 select 2,'数字' insert into #t2 select 3,'英语' select b.老师 from (select 老师,课程='['+replace(isnull(课程,''),' ','][')+']' from #t1) b where exists(select 1 from #t2 where 课程名='语文' and charindex('['+rtrim(课程号)+']',b.课程)>0) and exists(select 1 from #t2 where 课程名='数字' and charindex('['+rtrim(课程号)+']',b.课程)>0)
SELECT Teacher FROM Teacher_Source WHERE (CHARINDEX(CONVERT(varchar, (SELECT sourceID FROM source_info WHERE sourceDes = '语文')), source) > 0) AND (CHARINDEX(CONVERT(varchar, (SELECT sourceID FROM source_info WHERE sourceDes = '数学')), source) > 0)
insert into #t1 select '李老师','1 2'
insert into #t1 select '张老师',NULL
insert into #t1 select '齐老师','1 3 2'
create table #t2(课程号 int,课程名 varchar(10))
insert into #t2 select 1,'语文'
insert into #t2 select 2,'数字'
insert into #t2 select 3,'英语'
select
b.老师
from
(select 老师,课程='['+replace(isnull(课程,''),' ','][')+']' from #t1) b
where
exists(select 1 from #t2 where 课程名='语文' and charindex('['+rtrim(课程号)+']',b.课程)>0)
and
exists(select 1 from #t2 where 课程名='数字' and charindex('['+rtrim(课程号)+']',b.课程)>0)
老师 课程
李老师 1
李老师 2
张老师 NULL
齐老师 1
齐老师 3
齐老师 2
這樣不是很好嗎?
SELECT Teacher
FROM Teacher_Source
WHERE (CHARINDEX(CONVERT(varchar,
(SELECT sourceID
FROM source_info
WHERE sourceDes = '语文')), source) > 0) AND (CHARINDEX(CONVERT(varchar,
(SELECT sourceID
FROM source_info
WHERE sourceDes = '数学')), source) > 0)