我有两个表:
Students Student_Grades
V------------------------V
+----+------+ +----+------------+---------+-------+
| id | name | | id | student_id | subject | grade |
+----+------+ +----+------------+---------+-------+
| 0 | Dave | | 0 | 0 | Math | 100 |
+----+------+ +----+------------+---------+-------+
| 1 | John | | 1 | 0 | Chem | 90 |
+----+------+ +----+------------+---------+-------+
| 2 | Kate | | 2 | 0 | CompSCI | 95 |
+----+------+ +----+------------+---------+-------+
| 3 | Mimi | | 3 | 1 | ELA | 98 |
+----+------+ +----+------------+---------+-------+
| 4 | 2 | Biology | 92 |
+----+------------+---------+-------+
| 5 | 2 | Chem | 94 |
+----+------------+---------+-------+
| 6 | 2 | Math | 98 |
+----+------------+---------+-------+
| 7 | 3 | Math | 100 |
+----+------------+---------+-------+
我想从一个注册了三门以上科目的随机学生中选择所有科目和成绩。(Dave
或Kate
)
学生John
和Mimi
甚至不会被考虑,因为他们没有参加三门课程。
我知道我可以用PHP实现这一点,但我希望只需对数据库进行一次查询。
SELECT * FROM Students t JOIN (SELECT CEIL(MAX(ID)*RAND()) AS ID FROM Students) AS x ON t.ID >= x.ID LIMIT 1
通过以上查询,我随机选择了一名学生,这样我就可以进去检查他们是否有三门SELECT count(subjects) FROM Students WHERE id=random_id
科目。
如果返回的计数低于3,那么我将丢弃结果并再次运行第一个查询。
如何在一个查询中尝试此操作?
这是经过测试和工作的:
SELECT *
FROM Students s
JOIN (
SELECT student_id
FROM Student_Grades
GROUP BY student_id
HAVING COUNT(*) >= 3
ORDER BY RAND()
LIMIT 1
) rs
ON rs.student_id = s.id
JOIN
Student_Grades sg
ON sg.student_id = s.id
以下是SQL Fiddle:http://sqlfiddle.com/#!2/e5b5b/1