选修课程最多的学生:(通过建立视图)
create view maxstu as select s#,count(c#) as SNum from sc group by s#
select * from maxstu where snum=(select max(snum) from maxstu)
选修课程最多的科目:
create view maxkc as select c#,count(s#) as kcnum from sc group by sc.c#
select * from maxkc where KCNUM=(select max(kcnum) from maxkc)写的很麻烦,有点罗嗦,也希望大家给好意见
create view maxstu as select s#,count(c#) as SNum from sc group by s#
select * from maxstu where snum=(select max(snum) from maxstu)
选修课程最多的科目:
create view maxkc as select c#,count(s#) as kcnum from sc group by sc.c#
select * from maxkc where KCNUM=(select max(kcnum) from maxkc)写的很麻烦,有点罗嗦,也希望大家给好意见
SQL> select * from s; S# SNAME
---------- --------------------
9805100 sssss
9805101 aaaaa
9805102 bbbbb
9805103 ccccc
9805104 dddddSQL> select * from c; C# CNAME TEACHAR
---------- -------------------- --------------------
0 Math Li
1 English Shi
2 Graphics liu
3 Chinese WangSQL> select * from sc; S# C# GRADE
---------- ---------- --------------------
9805100 0 100
9805100 1 100
9805100 2 100
9805100 3 100
9805101 3 100
9805102 3 100
9805103 3 100
9805103 2 1008 rows selected.
SQL> @b.sql S# SNAME
---------- --------------------
9805100 sssss
C# CNAME TEACHAR
---------- -------------------- --------------------
3 Chinese Wang
b.sql
select s.* from s,(select rownum row_num,mys# s# from (select mys#,mycount from(select count(*)
mycount,s# mys# from sc group by s#) order by mycount desc) where rownum=1) b where s.s#=b.s#;
select c.* from c,(select rownum row_num,myc# c# from (select myc#,mycount from(select count(*)
mycount,c# myc# from sc group by c#) order by mycount desc) where rownum=1) b where c.c#=b.c#;
---------------------------------------
select s#,count(c#) from sc group by s#
where count(c#)>=all(select count(c#) from sc group by s#);
----------------------------------------------------------
其中,ALL后面的集合只有一个列.表示不同S#对应的选课数目.搞定!!
(第二个问题一样的).但是,如果列出学生的信息,就麻烦了--虽然,思路很简单.
另提:对于较少记录运行可以,但是多了,就要考虑sql 运行的速度了。搂主的写法我觉得不妙。
真想语不惊人死不休??
数据量一大,估计你那小小的服务器就要休眠了,,
改改表结构不好吗?
where count(c)=(select first 1 count(c) from sc group by s order by count(c) desc);SQLServer:select s, count(c) from sc group by s
where count(c)=(select top 1 count(c) from sc group by s order by count(c) desc);Oracle...