问题的模型:
有一张学生选课表TAB1,三个字段
id stu_no cou_no
主键(随机数) 学号 课程号这张表中存放了学生的选课信息,一个学生可以选择一门或多门课程,一门课程也可以由一个或多个学生选择,
不存在没有课程号的学号,也不存在没有学号的课程号。
现在要求找出这样的记录:三个或者三个以上的学生选择了三门或者三门以上的同样课程,把这样的记录存放
到TAB2表中:
比如:张三选择了数学、物理、化学,李四也选择了数学、物理、化学,王五选择了数学、物理、化学,赵六选择了物理;
那么将张三、李四、王五的学号和数学、物理、化学的课程号放在TAB2表中,不包含赵六的学号。表TAB2
id stu_no1 stu_no2 stu_no3 stu_no4 stu_no5 cou_no1 cou_no2 cou_no3 cou_no4 cou_no5
主键(随机生成)
即将三个或者三个以上的学生选择了三门或者三门以上的同样课程的信息放在同一条记录中!
我用的是ORACLE数据库。
求达人赐教!
有一张学生选课表TAB1,三个字段
id stu_no cou_no
主键(随机数) 学号 课程号这张表中存放了学生的选课信息,一个学生可以选择一门或多门课程,一门课程也可以由一个或多个学生选择,
不存在没有课程号的学号,也不存在没有学号的课程号。
现在要求找出这样的记录:三个或者三个以上的学生选择了三门或者三门以上的同样课程,把这样的记录存放
到TAB2表中:
比如:张三选择了数学、物理、化学,李四也选择了数学、物理、化学,王五选择了数学、物理、化学,赵六选择了物理;
那么将张三、李四、王五的学号和数学、物理、化学的课程号放在TAB2表中,不包含赵六的学号。表TAB2
id stu_no1 stu_no2 stu_no3 stu_no4 stu_no5 cou_no1 cou_no2 cou_no3 cou_no4 cou_no5
主键(随机生成)
即将三个或者三个以上的学生选择了三门或者三门以上的同样课程的信息放在同一条记录中!
我用的是ORACLE数据库。
求达人赐教!
--------------- ---------------
张三 数学
张三 物理
张三 化学
李四 数学
李四 物理
李四 化学
王五 数学
王五 物理
王五 化学
赵六 物理已选择10行。scott@ORA1> with a as (
2 select stu_no
3 from tab1
4 group by stu_no
5 having count(distinct cou_no) >= 3
6 ),
7 b as (
8 select cou_no
9 from tab1
10 group by cou_no
11 having count(distinct stu_no) >= 3
12 )
13 select *
14 from tab1
15 where stu_no in (select stu_no from a)
16 and cou_no in (select cou_no from b);STU_NO COU_NO
--------------- ---------------
李四 物理
李四 数学
李四 化学
王五 物理
王五 数学
王五 化学
张三 物理
张三 数学
张三 化学已选择9行。
SQL> SELECT * FROM TAB11; SID STU_NO COU_NO
---------- ---------- ----------
1 4301 1
2 4301 2
3 4301 3
4 4301 4
5 4302 1
6 4302 2
7 4302 3
8 4303 2
9 4303 1
10 4303 3
11 4304 111 rows selectedExecuted in 0.86 secondsSQL> CREATE OR REPLACE PROCEDURE SP_TEST_STU_SCORE(OUT_CURSOR OUT SYS_REFCURSOR) IS
2 CURSOR CUR_STUS IS
3 SELECT SID, STU_NO, COU_NO
4 FROM (SELECT SID, STU_NO, COU_NO, COUNT(1) OVER() COUNT2
5 FROM (SELECT SID,
6 STU_NO,
7 COU_NO,
8 COUNT(1) OVER(PARTITION BY STU_NO) COUNT1
9 FROM TAB11) ZZ
10 WHERE COUNT1 >= 3) YY
11 WHERE COUNT2 >= 9;
12
13 V_SQL VARCHAR2(10000) := '';
14 V_ROWTYPE TAB11%ROWTYPE;
15
16 BEGIN
17 V_SQL := 'SELECT ';
18 OPEN CUR_STUS;
19 LOOP
20 FETCH CUR_STUS INTO V_ROWTYPE;
21 EXIT WHEN CUR_STUS%NOTFOUND;
22 V_SQL := ' '||V_SQL||''''||V_ROWTYPE.COU_NO||''''||' "'||''||V_ROWTYPE.STU_NO||''||'" ,';
23 END LOOP;
24 CLOSE CUR_STUS;
25
26 V_SQL := RTRIM(V_SQL,',')||' FROM DUAL';
27
28 OPEN OUT_CURSOR FOR V_SQL;
29
30 EXCEPTION
31 WHEN OTHERS THEN
32 DBMS_OUTPUT.PUT_LINE(SQLCODE||' : '||SQLERRM);
33 END;
34 /Procedure createdExecuted in 0.797 secondsSQL> var v_out refcursor;
SQL> begin
2 SP_TEST_STU_SCORE(:v_out);
3 end;
4 /PL/SQL procedure successfully completedSQL> print :v_out;43 43 43 43 43 43 43 43 43 43
-- -- -- -- -- -- -- -- -- --
1 2 3 4 1 2 3 2 1 3
start with 1
increment by 1;insert into tab1 values(seq_sa_id.nextval,'1','1');
insert into tab1 values(seq_sa_id.nextval,'1','2');
insert into tab1 values(seq_sa_id.nextval,'1','3');
insert into tab1 values(seq_sa_id.nextval,'1','4');
insert into tab1 values(seq_sa_id.nextval,'2','1');
insert into tab1 values(seq_sa_id.nextval,'2','3');
insert into tab1 values(seq_sa_id.nextval,'2','4');
insert into tab1 values(seq_sa_id.nextval,'3','1');
insert into tab1 values(seq_sa_id.nextval,'3','2');
insert into tab1 values(seq_sa_id.nextval,'3','3');
insert into tab1 values(seq_sa_id.nextval,'3','4');
insert into tab1 values(seq_sa_id.nextval,'4','1');
insert into tab1 values(seq_sa_id.nextval,'4','4');
insert into tab1 values(seq_sa_id.nextval,'4','5');
insert into tab1 values(seq_sa_id.nextval,'5','1');commit;
select decode(数学, 1, '数学', null) 数学,
decode(语文, 2, '语文', null) 语文,
decode(英语,3,'英语',null) 英语,
decode(化学,4,'化学',null) 化学,
decode(地理,5,'地理',null) 地理,
decode(avg(decode(stu_no, '1', stu_no, null)),1,'张三',2,'李四',3,'王五',4,'赵六',5,'陈七',null) "学生1",
decode(avg(decode(stu_no, '2', stu_no, null)),1,'张三',2,'李四',3,'王五',4,'赵六',5,'陈七',null) "学生2",
decode(avg(decode(stu_no, '3', stu_no, null)),1,'张三',2,'李四',3,'王五',4,'赵六',5,'陈七',null) "学生3",
decode(avg(decode(stu_no, '4', stu_no, null)),1,'张三',2,'李四',3,'王五',4,'赵六',5,'陈七',null) "学生4",
decode(avg(decode(stu_no, '5', stu_no, null)),1,'张三',2,'李四',3,'王五',4,'赵六',5,'陈七',null) "学生5" from (select stu_no,
sum(decode(cou_no, 1, cou_no, null)) "数学",
sum(decode(cou_no, 2, cou_no, null)) "语文",
sum(decode(cou_no, 3, cou_no, null)) "英语",
sum(decode(cou_no, 4, cou_no, null)) "化学",
sum(decode(cou_no, 5, cou_no, null)) "地理"
from tab1
group by stu_no)
group by 数学, 语文, 英语, 化学, 地理;