时间戳

首页 > 解决方案 > 如何根据学院名称显示学生人数和显示每个学院的学生总数

join - 如何根据学院名称显示学生人数和显示每个学院的学生总数

问题描述

表学院：

块报价

create table college
(
    clg_code number(3) primary key,
    clg_name varchar(20)
);

表 stud_detail

块报价

create table stud_detail
(
    stud_no number(3) primary key,
    name varchar(10),
    dob date,
    clg_code references college
);

我试过这个触发器，现在必须计算每个学院的学生总数。

块报价

set serveroutput on;

声明游标 c_stud 是 select stud_no,name,clg_name from stud_detail s,college c where s.clg_code=c.clg_code;

    v_stud c_stud%rowtype;

begin
    for v_stud in c_stud
    loop
            dbms_output.put_line(v_stud.stud_no||' '||v_stud.name||' '||v_stud.clg_name);

    end loop;
end;
/

大学表中的值

块报价

insert into college values (101,'Sahjanand');
insert into college values (102,'Gurukul');
insert into college values (103,'K.R.Doshi');

块报价

从大学中选择*；

stud_detail 中的值

块报价

insert into stud_detail (name,dob,clg_code)values('abc','12-mar-1998',101); 
insert into stud_detail (name,dob,clg_code)values('adsfbc','22-jan-1999',101);  
insert into stud_detail (name,dob,clg_code)values('ac','13-feb-1995',101);  
insert into stud_detail (name,dob,clg_code)values('ddbc','02-mar-1998',101);    
insert into stud_detail (name,dob,clg_code)values('afdgc','09-sep-1996',101);   
insert into stud_detail (name,dob,clg_code)values('adf','30-jun-1997',101); 
insert into stud_detail (name,dob,clg_code)values('osif','24-mar-1996',101);    

insert into stud_detail (name,dob,clg_code)values('dfif','13-mar-1996',102);    
insert into stud_detail (name,dob,clg_code)values('odffif','26-jan-1993',102);  
insert into stud_detail (name,dob,clg_code)values('fsaf','30-mar-1994',102);    
insert into stud_detail (name,dob,clg_code)values('vvhgf','08-jul-1995',102);   
insert into stud_detail (name,dob,clg_code)values('odgf','19-sep-1997',102);    
insert into stud_detail (name,dob,clg_code)values('dfgfif','12-oct-1998',102);  
insert into stud_detail (name,dob,clg_code)values('dfgdif','24-feb-1996',102);  
insert into stud_detail (name,dob,clg_code)values('sdgfif','21-aug-1998',102);  

insert into stud_detail (name,dob,clg_code)values('jc','22-mar-1994',103);  
insert into stud_detail (name,dob,clg_code)values('charmi','26-dec-1998',103);
insert into stud_detail (name,dob,clg_code)values('ritu','04-dec-1991',103);
insert into stud_detail (name,dob,clg_code)values('ridddhi','26-may-1998',103);
insert into stud_detail (name,dob,clg_code)values('khushbu','11-jul-1998',103);
insert into stud_detail (name,dob,clg_code)values('vaishali','23-feb-1999',103);

块报价

从 stud_detail 中选择 *；

stud_detail 的主键生成触发器。

块报价

create or replace trigger tristud before insert on stud_detail for each row declare     pkey number(5); begin   select max(stud_no)+1 into pkey from stud_detail;   if (pkey is null) then      :new.stud_no:=1;    else        :new.stud_no:=pkey;     end if; end; /

标签： joinplsqltriggersplsqldevelopercursors

解决方案

我猜，你只需要一个简单的COUNT

SQL 查询 -

select clg_name, count(stud_no) count_of_studens
  from college c
  join stud_detail s
    on s.clg_code = c.clg_code
group by clg_name;

在匿名块中 -

declare

cursor stud_record is 
select clg_name, count(stud_no) count_of_studens
  from college c
  join stud_detail s
    on s.clg_code = c.clg_code
group by clg_name;
BEGIN
   for records in stud_record
   loop
       dbms_output.put_line('College Name - ' || records.cld_name);
       dbms_output.put_line('Number of Students - ' || records.count_of_students);
   END FOR;

END;
/

ps你真的应该使用主键序列而不是触发器。

推荐阅读