如题所述
1、创建两张测试表,
create table test_student(student_id varchar2(20), student_name varchar2(20));
create table test_class(student_id varchar2(20), class_id varchar2(20));
2、插入测试数据
insert into test_student values(1001,'陈XX');
insert into test_student values(1002,'许XX');
insert into test_student values(1003,'张XX');
insert into test_student values(1004,'吴XX');
insert into test_class values (1001,'C01');
insert into test_class values (1001,'C02');
insert into test_class values (1002,'C01');
insert into test_class values (1003,'C01');
insert into test_class values (1004,'C01');
insert into test_class values (1004,'C02');
3、查询表的记录,select t.*, rowid from test_class t;
4、编写sql,查询出来test_student表中在test_class表中是多条而不是单条的记录,可以看到1001、1004学生是有多条记录的,
select t.student_id,
count(1) a
from test_student t , test_class b
where t.student_id = b.student_id
group by t.student_id
having count(*)>1,