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.