Situation: such as now there is A A city all school students information table student (1000 w), whether classmates before now needs between students and students (condition is confronting) same level, so the question becomes, since this student and student association, will produce A lot of correlation [50 people, such as A class x and 49 other personal relationship] is generated and lead to low efficiency, could you tell me is there A better way to solve? ! Consult!

CodePudding user response:

Need to use a table, change the idea, first find out two students all schools, which, class, and then according to these three groups, if there is greater than 2 records are classmates, otherwise not.

 select * from (
Select count (1) the nm, stu. School id, stu. Id, the level to which the stu. Class 
The from student stu 
Where stu. Id=Anderson d or stu. Id=B.i d 
Group by stu. School id, stu. Id, the level to which the stu.) class b where b.n m> 1 

CodePudding user response:

Don't need to use associative table, top wrong

CodePudding user response:

reference 1st floor panxuying123 response:

need to use a table, change the idea, first find out two students all schools, which, class, and then according to these three groups, if there is greater than 2 records are classmates, otherwise not.

 select * from (
Select count (1) the nm, stu. School id, stu. Id, the level to which the stu. Class 
The from student stu 
Where stu. Id=Anderson d or stu. Id=B.i d 
Group by stu. School id, stu. Id, the level to which the stu.) class b where b.n m> 1 

First of all thank you for your reply! But I demand inside is needed in the same record to show the relationship between two student
Such as student A year enrolled student B XXX XXX for students enrolled relationship class level school such format, if it's not anything, how to do?

CodePudding user response:

There should be a class's and grade's students belong to the history table right, so the question is simple
Suppose this form for T table,
Select the class where students primary key information from t=ID
To find out the history of the student class information
So, we take the intersection of two students
Select the class where students primary key information from t=student A
Intersects
Select the class information from t where students primary key=B

Query results, the coincidence of class, is for the students of class with them, if there is no return record, there is no classmates