求SQL语句

[sdulq]

有一课程表dc_course，字段subjno表示课程编号，clscode表示班级编号。
subjno clscode
0025 2002001 －－－－表示2002001这个班上0025这门课
0025 2002002
0035 2002001
0035 2002002
0035 2002003
0045 2002001
0045 2002002
0055 2002002
要求：对给定的subjno值，查询得到subjnoX使得subjnoX和给定的subjno有完全相同的班级编号clscode。 例如对上面的例子，给定subjno＝0025，能得到的subjno是0045。 谢谢！

[gmlxf]

select subjno from dc_course
where clscode in
(
select clscode from dc_course where subjno='0045'
)

--??

[gmlxf]

select subjno from dc_course
where clscode in
(
select clscode from dc_course where subjno='0025'
)

[sunshareforever]

select subjno from dc_course
where clscode in (select clscode from dc_course where subjno ='0045')

[pengdali]

select subjno from dc_course where subjno<>'0025' clscode in (select clscode from dc_course where subjno='0025')
and group by subjno having count(*)=(select count(*) from dc_course where subjno='0025')

[shuiniu]

select subjno from dc_course where clscode = (select clscode from dc_course where subjno = '0025') and subjno <> '0025'
--
？？

[pengdali]

select subjno from dc_course where subjno<>'0025' and clscode in (select clscode from dc_course where subjno='0025')
group by subjno having count(*)=(select count(*) from dc_course where subjno='0025')

[gmlxf]

select subjno from dc_course
where clscode in
(
select clscode from dc_course where subjno='0025'
)
and subjno<>'0025'
--??

[nobelpu]

SELECT subjno
FROM dc_course
WHERE EXISTS(SELECT 1 FROM dc_course AS A WHERE A.clscode = dc_course.clscode )
AND subjno = '{InputNo}'

[txlicenhe]

create table dc_course(subjno char(4),clscode char(7))
insert dc_course select '0025','2002001'
union all select '0025','2002002'
union all select '0035','2002001'
union all select '0035','2002002'
union all select '0035','2002003'
union all select '0045','2002001'
union all select '0045','2002002'
union all select '0055','2002002'

select subjno from dc_course
group by subjno having count(*)=(select count(*) from dc_course where subjno='0025')
and subjno not in (Select subjno from dc_course where clscode not in (select clscode from dc_course where subjno = '0025') )
and subjno <> '0025'

subjno
------
0045

（所影响的行数为 1 行）

[zjcxc]

--定义要查询的数据
declare @subjno varchar(4)
set @subjno='0025'

--数据处理
select subjno,count(*) from dc_course a left join (
select clscode from dc_course where subjno=@subjno
) b on a.clscode=b.clscode
where a.subjno<>subjno
group by subjno
having count(*)=(select count(*) from dc_course where subjno=@subjno)

[zjcxc]

--测试表
declare @dc_course table(subjno varchar(4),clscode varchar(8))
insert into @dc_course
select '0025','2002001'
union all select '0025','2002002'
union all select '0035','2002001'
union all select '0035','2002002'
union all select '0035','2002003'
union all select '0045','2002001'
union all select '0045','2002002'
union all select '0055','2002002'

--定义要查询的数据
declare @subjno varchar(4)
set @subjno='0025'

--数据处理
select subjno,count(*) from @dc_course a left join (
select clscode from @dc_course where subjno=@subjno
) b on a.clscode=b.clscode
where a.subjno<>@subjno
group by subjno
having count(*)=(select count(*) from @dc_course where subjno=@subjno)

/*--测试结果
subjno
------ -----------
0045 2

（所影响的行数为 1 行）

--*/

[zjcxc]

--定义要查询的数据
declare @subjno varchar(4)
set @subjno='0025'

--数据处理,count(*) 是没必要写的,忘记删除了.改一下
select subjno from dc_course a left join (
select clscode from dc_course where subjno=@subjno
) b on a.clscode=b.clscode
where a.subjno<>subjno
group by subjno
having count(*)=(select count(*) from dc_course where subjno=@subjno)

[sdulq]

谢谢大家！txlicenhe(马可) 的查询完全正确，多谢！zjcxc(邹建) 的查询仅仅是用clscode的数量约束了一下。也许是我没把问题说清楚，也许是我举的例子不是太典型，但是还是要谢谢大家～俺第一次发帖子问问题就能得到答案，高兴～