嵌套查询的问题

[yellowtree12]

若已知“学生”表，“班级”表，查看与“A1010601”班级人数相等的班级名称和班长姓名。该怎么查询？

[sgtzzc]

select d.班级名称,c.学生姓名 as 班长姓名
from
(select count(1) as cnt from 学生 where 班级号='A1010601') a,
(select 班级号,count(1) as cnt from 学生 where 班级号!='A1010601') b,
学生 c,
班级 d
where
a.cnt=b.cnt and b.班级号=c.班级号 and b.班级号=d.班级号
and
c.职务='班长'

[rucypli]

select 班级
from (
select 班级,count(*) as 人数
from 学生
group by 班级
)T
where T.人数=(
select count(*) as 人数
from 学生 A
where T.班级='A1010601')

[icelovey]

-- =============================================
-- Author:      T.O.P
-- Create date: 2009/11/29
-- Version:     SQL SERVER 2005
-- =============================================
declare @tb1 table([班级] varchar(1),[学生] varchar(2))
insert @tb1
select 'A','X1' union all
select 'A','X2' union all
select 'B','X3' union all
select 'B','X4' union all
select 'C','X5'

declare @tb2 table([班级] varchar(1),[班长] varchar(2))
insert @tb2
select 'A','B1' union all
select 'B','B2' union all
select 'C','B3'



SELECT T1.[班级], T1.[班长]
FROM (
		SELECT a.[班级], a.[班长],count(b.[班级]) AS 人数
		FROM @tb1 b, @tb2 a
		where a.[班级] = b.[班级]
		group by a.[班级],a.[班长]
	 ) T1
WHERE t1.人数 = (select count(*) from @tb1 where [班级]='A') and t1.[班级]<>'A'
--测试结果:
/*
(3 row(s) affected)
班级   班长
---- ----
B    B2

(1 row(s) affected)
*/