Geek Answers Handbook

Help me write a sql expression to do something like this

You can also write hibernate hql or query criteria.

I have an object "Teacher" and "Student":

class Teacher {
     public Long id ;
     public Set<Student> students;
} 

class Student {
    public Long id ;
    public Teacher teacher ;
    public Boolean passedSemester1;  
    public Boolean passedSemester2; 
}

You can assume that my tables have the following structure.

The teacher and student have a bi-directional connection in many ways. The student table controls the foreign key.

I need to get to know the Masters whose students have completed semester1 and semester2. In fact, I also need to look for:

all did not pass both semesters1 and semester2, everything passed semester1, but semester2 did not pass, all did not pass semester1, but semester2 passed.

You can write any of these queries, others should not have big differences.

, . , , , .

!

+5
1

SQL, , :

SELECT teacher_id
FROM student
GROUP BY teacher_id
HAVING MIN(passed_semester_1) AND MIN(passed_semester_2)

:

1
4

, 1, 2:

SELECT teacher_id
FROM student
GROUP BY teacher_id
HAVING MIN(passed_semester_1) AND NOT MIN(passed_semester_2)

:

2

:

SELECT T2.*
FROM (
    SELECT teacher_id
    FROM student
    GROUP BY teacher_id
    HAVING MIN(passed_semester_1) AND MIN(passed_semester_2)
) AS T1
JOIN teacher AS T2 ON T1.teacher_id = T2.teacher_id

:

CREATE TABLE student (student_id INT NOT NULL, teacher_id INT NOT NULL, passed_semester_1 INT NOT NULL, passed_semester_2 INT NOT NULL);
INSERT INTO student (student_id, teacher_id, passed_semester_1, passed_semester_2) VALUES
(1, 1, 1, 1),
(2, 1, 1, 1),
(3, 1, 1, 1),
(4, 2, 1, 1),
(5, 2, 1, 0),
(6, 2, 1, 1),
(7, 3, 0, 1),
(8, 3, 1, 1),
(9, 4, 1, 1);
+1

More articles:


All Articles