C_J_U_L_Good
Max_inner
2017-10-27 03:08

Oracle 自然连接后投影重复

10
  • oracle
  • 异常
  • sql

** oracle 10.2.1,PL/SQL**

具体要求: _ 查询学分修满25分的同学姓名和学分详情_

表结构,忽略约束条件

--学生信息表:student_info
create table student_info
(
    stuid      int,                 --学号
    stuname    varchar2(20),            --姓名
    stusex     char(4),                 --性别
    birthday   date default (sysdate),  --出生年月 
    cellphone  int,                 --手机号
    address    varchar2(40),            --家庭地址
        Email      varchar2(20),            --邮箱
        classno    int                  --所属班级
);
commit;

--课程信息表:course_info
create table course_info
(
    cno         varchar2(10),         --课程编号
    cname       varchar2(20),         --课程名称
    cstart      int,                --课程开始学期
    cperiod     int,                  --课时
    ccredits    number(2,1)           --学分
);
commit;

--成绩信息表: score_info
create table score_info
(
    stuid       int,         --学号
    cno         varchar2(10),        --课程编号
    score       int          --成绩
);
commit;
  1. select *
select *
        from (student_info natural join score_info) natural join course_info
        where stuid in (select stuid
                        from score_info natural join course_info
                        group by stuid
                            having sum(ccredits)>=25
                        );

输出,此时并无重复

  1. select stuname,ccredits
select stuname,ccredits
    from (student_info natural join score_info) natural join course_info
        where stuid in (select stuid
                        from score_info natural join course_info
                        group by stuid
                            having sum(ccredits)>=25
                            );

输出,此时出现重复

  1. 当改为下面语句时正常
select stuname,ccredits 
    from ((select *
    from (student_info natural join score_info) natural join course_info
    where stuid in (select stuid
            from score_info natural join course_info
            group by stuid
              having sum(ccredits)>=25
            )

输出,无异常

提问:如何解决 2例 中的重复问题?

  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

1条回答

为你推荐

换一换