Question

ORACLE SQL语句查询一个字段在另一表字段中有两条或以上 的数据

Answer 1

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，