SQL issue,challenge

Say we have two entities:teacher and student.

each teacher has multiple student.

Now I want to:

q开发者_Python百科uery for at most 5 teachers,and for each teacher, no more than 10 of his student.

So far this can be done quite easily by:

select *,
(
select GROUP_CONCAT('<sid>',students.name,'</sid>') from students on 
teachers.id=students.teacher limit 10
) as students 
from teachers limit 5

But that's not the whole story yet.

AND

If anyone of the teachers has more than 10 students,should return true for that teacher,otherwise false

How to do this in SQL?

Pseudo SQL: one row of results for each of the 10 students for each of the 5 teachers

select t.teacher_id, s.student_id, 
    case when t2.count > 10 then 'true' else 'false' end
from
    (select top 5 * 
    from teachers 
    order by teacher_id) t 
join 
    (select top 10 * 
    from students s1 join teachers t1 on s1.teacher_id = t1.teacher_id 
    order by student_id) s 
on t.teacher_id = s.teacher_id
join
    (select teacher_id, count(*) as count 
    from teachers t join students s on t.teacher_id = s.teacher_id 
    group by teacher_id)  t2 
on t2.teacher_id = t.teacher_id

LINQ makes this super-easy

Use a subquery for every teacher selected.