这个 SQl 怎么写？

[slfjlj]

学生表：stu（表名） 字段名：s_id，s_name（学生名称），s_cid（班级id）

班级表：class（表名） 字段名： c_id，c_name（班级名称）

说明：s_cid 和 c_id 相对应

查询 结果：学生名称，班级名称。

条件：1、班级人数大于20。2、班级名称有“三”的。3、按班级人数降序排列。

[chuanzhang5687]

select s_sname,c_name from stu,class where s_cid=c_id and s_cid in(
select s_cid from stu group by s_cid having count(*)>20 )and c_name like %三%

[Rotel-刘志东]

---1、班级人数大于20。。
select count(distinct a.s_name)t,b.c_name from stu a,class b 
where a.s_cid=b.c_id 
group by b.c_name
having count(distinct a.s_name)>20
---2、班级名称有“三”的
select  a.s_name,b.c_name from stu a,class b 
where a.s_cid=b.c_id  and b.c_name like'%三%'
-----3、按班级人数降序排列。
select count(distinct a.s_name)t,b.c_name from stu a,class b 
where a.s_cid=b.c_id 
group by b.c_name
order by t desc

select b.c_id,b.c_name from class b

[PitTroll]

1.select s.s_name,c.c_name from stu as s join class as c on s.s_cid=c.c_id and s.s_id in (select s_id from stu group by s_id having count(s_id)>20)

[slfjlj]

没有按人数进行降序排列啊

[slfjlj]

能不能用一条sql语句求出结果啊

[investruth]

3个条件是并列的？还是分别有3个？

[chuanzhang5687]

人数它不是一个列 你查询结果又没有人数这一列 这让人很蛋疼啊

[slfjlj]

条件是并列的

[Spade_J]

select * from 
(select s_sname,c_name from stu,class where s_cid=c_id) a,
(select s_cid,count(1) as sNo from stu group by s_cid having count(1)>20) b
where 
a.s_cid=b.s_cid
order by b.sNo desc

[Spade_J]

select s_sname,c_name from 
(select stu.s_sname,class.c_name,stu.s_cid from stu,class where stu.s_cid=class.c_id) a,
(select s_cid,count(1) as sNo from stu group by s_cid having count(1)>20) b
where 
a.s_cid=b.s_cid
order by b.sNo desc

[oO寒枫Oo]

[Quote=引用楼主 pengfuhong 的回复:]
学生表：stu（表名） 字段名：s_id，s_name（学生名称），s_cid（班级id）

班级表：class（表名） 字段名： c_id，c_name（班级名称）

说明：s_cid 和 c_id 相对应

查询 结果：学生名称，班级名称。

条件：1、班级人数大于20。2、班级名称有“三”的。3、按班级人数降序排列。
[/Quote]

select a.s_name, c.c_name from stu  a inner join 
(select count(1) as s_num , s_cid from stu group by s_cid) b on a.s_cid=b.s_cid
inner join class c on a.s_cid=c.c_id 
where b.s_num>20 and c.c_name like '%三%' 
order by b.s_num desc

[slfjlj]

谢 11楼

[slfjlj]

还有10楼的 他写那个在改下

select s_name, c_name from
(select s_name,s_cid,c_name from stu,class where s_cid=c_id) a,
(select s_cid,count(1) as sNo from stu group by s_cid having count(1)>5) b
where
a.s_cid=b.s_cid
and a.c_name like '%班%'
order by b.sNo desc

谢大家了 结贴了

[rjthinking]

with temptable as
(
	select s_name ,c_name from stu,class where s_cid=c_id and c_name like '%三%' and c_id in
	(
		select c_id from stu,class where s_cid = c_id 
		group by c_id having(count(s_id)>=20) 
	)
)
select s_name ,c_name,
(select count(c_name) from temptable te where te.c_name = tt.c_name) as cal --计算出班级
from temptable tt order by cal  desc

[So_CooL]

select s_name,c_name from stu  a left join class b
on a.s_cid = b.c_id 
where count(a.c_name)> 20 and charindex('三',c_name)> 0 
order by count(a.c_name) desc

[csjack]

Select a.s_name,b.c_name
From stu a Inner Join class b On a.s_cid=b.c_id
Inner Join ( Select s_cid,Count(s_id) as StudentNum From stu Group By s_cid Haveing Count (s_id)>20) c
c.s_cid=a.s_cid
Where b.c_name like '%三%'
Order by c.StudentNum Desc

[csjack]

Select a.s_name,b.c_name
From stu a Inner Join class b On a.s_cid=b.c_id
Inner Join ( Select s_cid,Count(s_id) as StudentNum From stu Group By s_cid Haveing Count (s_id)>20) c ON
c.s_cid=a.s_cid……

[csjack]

以上16楼回帖时忘了加 “On”在“c” 和“c.s_cid=a.s_cid”之间了