Mysql的最大查询
问题描述:
我有两个表:Mysql的最大查询
teachers (teacher_id,teacher_name)
courses (teacher_id,course_id)
,我需要显示的老师的名字,教学课程的最大数量:
的mysql>选择teachers.teacher_name,tmp1.teacher_id,TMP1 .cnt from(选择max(tm p.cnt)as tmpMax from(选择teacher_id,count(teacher_id)as cnt from course g groups by teacher_id)as tmp)as tmp2,(选择teacher_id,count(teacher_id)as cnt from course group by teacher_id)as tmp1,teachers where tmp1.cnt = tmp2.tmpMax and teac hers.teacher_id = tmp1.teacher_id;
我想出了上述查询。有没有更简单的查询这个问题?
答
这应该工作:
select teacher_name
from teachers
where teacher_id IN
(
select t.teacher_id
from teachers t inner join courses c on t.teacher_id = c.teacher_id
group by t.teacher_id
having count(*) =
(
select max(courses_per_teacher) from
(
select teacher_id, count(*) as courses_per_teacher
from teachers t inner join courses c on t.teacher_id = c.teacher_id
group by teacher_id
)
)
)
逻辑伪代码:
- 找到老师名ID分别在 - >
- ,其数量的一批教师的课程是 - >
- 等于最大数量 - >
- 的每名教师
希望帮助课程编号列表。
答
SELECT teacher_id,TEACHER_NAME,COUNT(1)course_count
教师
WHERE course_count =(SELECT MAX(COUNT(1))FROM课程GROUP BY teacher_id)
GROUP BY teacher_id
答
给这个一个镜头:
select a.teacher_name as 'Teacher', count(b.course_id) as 'Num of Courses'
from teachers a
inner join courses b on a.teacher_id = b.teacher_id
group by b.teacher_id
order by count(b.course_id) desc
编辑 下面将为您完全相同的结果作为查询:
select a.teacher_name as 'Teacher', a.teacher_id as 'Teacher Id', count(b.course_id) as 'Num of Courses'
from teachers a
inner join courses b on a.teacher_id = b.teacher_id
group by b.teacher_id
order by count(b.course_id) desc
limit 1
错误1054(42S22):'where子句'中的未知列'course_count' – dharm0us 2009-04-19 05:59:41