- create a table s, c, sc, s # is student id, name sn, sd, sa age; C # is the course number, cn course; G score
The create table (s
S # integer primary key,
Sn varchar2 (10),
Sd varchar2 (10),
Sa integer
);

The create table c (
C # integer primary key,
Cn varchar2 (10)
);

The create table sc (
S # integer,
C # is an integer,
G number (3, 1),
Primary key (s #, c #)
);
Insert into s values (23, 'zhangsan, tencent, 26).
Insert into s values (24, 'lisi', 'ali, 26).
Insert into s values (25, wangwu, tencent, 28).
Insert into s values (26, "Bob", null, 29).
Insert into c values (1, 'accounting');
Insert into c values (2, 'tax base);
Insert into c values (3, 'statistics');
Insert into sc values (23,1,85.5);
Insert into sc values (23,2,90);
Insert into sc values (23,3,78);
Insert into sc values (24,1,80);
Insert into sc values (24,3,95);
Insert into sc values (25,1,90);
Insert into sc values (25,2,84);
Insert into sc values (26,2,70);
Insert into sc values (26,3,87);

- find out students of more than 2 student number and unit
1
-The select s.s #, s.s d from sc inner join s on sc. S # #=s.s
Group by s.s #, s.s d having count (sc. C #) & gt; 2;
2
-The select s.s #, s.s d from s where s.s # in (select sc. S # from sc group by sc. S # having count (sc. C #) & gt; 2);

Although 2 spelled out the results of the same, there is a question I hope you help me answer the great god, according to the first writing, so that the grouping method have a wrong, wrong in where, trouble to explain in detail,

CodePudding user response:

Sorry, write wrong, basically be to see writing 1, where is the error

CodePudding user response:

Personal idea, when you are in a small amount of data's execution efficiency is better than 2, 1 but more data volume 2 is ok with 1

Two table 1 is set a table and then grouping, 2 is from the list number first, then combined with another,

When the small amount of data memory and CPU is enough, so fast

CodePudding user response:

Both writing is right,

It is more natural, understand the second

For execution plan
First should walk a HASH JOIN, the second NESTED LOOP

CodePudding user response:

reference wmxcn2000 reply: 3/f

both written is right,

It is more natural, understand the second

For execution plan
First should walk a HASH JOIN, the second NESTED LOOP

Two I don't understand what you have said, from the perspective of group by, on the basis of one field and two field grouping result sets should not be the same, is the first approach the right idea, please, there are several similar are made of this line of thinking, I fear of wrong ideas is in trouble,

CodePudding user response:

reference 4 floor qigedonglongqiang response:

two I don't understand you said, from the perspective of group by, according to the results of a field and two field grouping sets should not be the same, is the first approach the right idea, please, there are several similar are made of this line of thinking, I fear of wrong ideas is in trouble,

The second, is to find out course is greater than all the student id (equal) 2, according to the student id, go to the student table to retrieve the name and department

CodePudding user response:

You can turn the second statement of the subquery alone run, to see what effect;

You can generate some data and looking at more clearly;

CodePudding user response:

The select s.s #, s.s d from s where s.s # in (select sc. S # from sc group by sc. S # having count (sc. C #) & gt; 2);

1, first perform
The select sc. S # from sc group by sc. S # having count (sc. C #) & gt; 2
2, find a course more than 2 student id of the
The from # 3, select s.s, s.s d s where s.s # in a collection of (first step)
4, select elective greater than 2 classes of students information?

CodePudding user response:

1 and 2 are the result of the different? # 1 group by s.s, s.s d, 2 group by s.s # whether reason in it

CodePudding user response:

refer to the eighth floor cnceohjm response:

1 and 2 are the result of the different? # 1 group by s.s, s.s d, 2 group by s.s # if it's because of the this

Is the result is the same, two different grouping theory should be there is a difference, but why is the same as the actual result

CodePudding user response:

refer to 6th floor wmxcn2000 response:

you can turn the second statement of the subquery alone run, to see what effect;

You can generate some data and looking at more clearly;

Second this is no problem, it is very normal of nested, the problem is the first, my question is 1 group by s.s #, s.s d, 2 group by s.s # should be different, why is the result

CodePudding user response:

references to the tenth floor qigedonglongqiang response:

Quote: refer to the sixth floor wmxcn2000 response:

You can turn the second statement of the subquery alone run, to see what effect;

You can generate some data and looking at more clearly;

Second this is no problem, it is very normal of nested, the problem is the first, my question is 1 group by s.s #, s.s d, 2 group by s.s # should be different, why the results

You don't group by the first and only link the two tables, you look at the output, see if you can understand.