这句sql应该怎么写？

Question

对sql比较头疼，请教各位一下有这样一张表学生表Student序列  学生姓名     专业
id   studentName  majorId ....
1     张三          5
2     李四          5
3     王五          1
3     王五          1
3     王五          3
3     王五          4
还有这样一张专业表Major
序列   专业名      父类Id
id    majorName     pId ....
1      农业          -1
2      园艺          1
3    植物保护学      1
4     森林资源       1
5     环境生态       1
6      茶学          1专业表中的专业一共被分为了两级，两级放在同一张表中，如果pId字段的值是-1，则证明其是大类，否则是小类。而学生表中的majorId 字段中保存的，既可能是大类专业、也可能是小类专业现在要求根据专业的大类，对学生人数进行分组排序，这句sql应该怎么写？多谢各位。

Answer 1

select root,count(a.id) cnt
from
(
select s.id,t.root
from
(
select m.*,connect_by_root(majorname) root
from major m
start with pid = -1
connect by prior id = pid
) t,student s
where s.majorid = t.id
) a
group by root--例子
SQL> select * from student;
 
        ID STUDENTNAME             MAJORID
---------- -------------------- ----------
         1 张三                          5
         2 李四                          5
         3 王五                          1
         3 王五                          3
         3 王五                          4
 
SQL> select * from major;
 
        ID MAJORNAME                   PID
---------- -------------------- ----------
         1 农业                         -1
         2 园艺                          1
         3 植物保护学                    1
         4 森林资源                      1
         5 环境生态                      1
         6 茶学                          1
 
6 rows selected
SQL> select root,count(a.id) cnt
  2  from
  3  (
  4  select s.id,t.root
  5  from
  6  (
  7  select m.*,connect_by_root(majorname) root
  8  from major m
  9  start with pid = -1
 10  connect by prior id = pid
 11  ) t,student s
 12  where s.majorid = t.id
 13  ) a
 14  group by root
 15  ;
 
ROOT                        CNT
-------------------- ----------
农业                          5

Answer 2

SQL> select * from student;        ID STUDENTNAME             MAJORID
---------- -------------------- ----------
         1 张三                          5
         2 李四                          5
         3 王五                          1
         4 赵六                          1
         5 钱七                          3
         6 文八                          4
         7 何九                          8
         8 周一                          7
         9 刘十                          7
        10 胡快                          9
        11 陈云                          8已选择11行。SQL> select * from major;        ID MAJORNAME                             PID
---------- ------------------------------ ----------
         1 农业                                   -1
         2 园艺                                    1
         3 植物保护学                              1
         4 森林资源                                1
         5 环境生态                                1
         6 茶学                                    1
         7 工业                                   -1
         8 生活用品                                7
         9 生产工具                                7已选择9行。SQL> select majorname,count(*) from(
  2  select t1.studentname,t3.majorname
  3  from student t1,major t2,major t3
  4  where t1.majorid=t2.id and t2.pid=t3.id
  5  union all
  6  select t4.studentname,t5.majorname from
  7  student t4,major t5
  8  where t4.majorid=t5.id and t5.pid=-1)
  9  group by majorname;MAJORNAME                        COUNT(*)
------------------------------ ----------
工业                                    5
农业                                    6