3个表
表1
教师基本信息表 teacher_base
id teacher_id department_id teacher_name
1 8001 1 教师1
2 8002 1 教师2
3 8003 2 教师3
3 8004 2 教师4
3 8005 3 教师5
表2
部门信息表
id department_id department_name
1 1 科系1
2 2 科系2
3 3 科系3
4 4 科系4
表3
教师签到信息表
id teacher_id qiandao_date
1 1 2007-01-01
2 2 2007-01-02
3 1 2007-01-02
4 1 2007-01-03
5 3 2007-01-03
6 1 2007-01-04
7 2 2007-01-04
8 1 2007-01-05得到的结果需要:
id teacher_name department_name 合计签到节数
这样的过程需要怎么来写呢?
表1
教师基本信息表 teacher_base
id teacher_id department_id teacher_name
1 8001 1 教师1
2 8002 1 教师2
3 8003 2 教师3
3 8004 2 教师4
3 8005 3 教师5
表2
部门信息表
id department_id department_name
1 1 科系1
2 2 科系2
3 3 科系3
4 4 科系4
表3
教师签到信息表
id teacher_id qiandao_date
1 1 2007-01-01
2 2 2007-01-02
3 1 2007-01-02
4 1 2007-01-03
5 3 2007-01-03
6 1 2007-01-04
7 2 2007-01-04
8 1 2007-01-05得到的结果需要:
id teacher_name department_name 合计签到节数
这样的过程需要怎么来写呢?
id teacher_id department_id teacher_name
1 8001 1 教师1
2 8002 1 教师2
3 8003 2 教师3
3 8004 2 教师4
3 8005 3 教师5id 怎么回事,什么关系?
id teacher_id department_id teacher_name
1 8001 1 教师1
2 8002 1 教师2
3 8003 2 教师3
3 8004 2 教师4
3 8005 3 教师5的ID 写错了。应该是教师基本信息表 teacher_base
id teacher_id department_id teacher_name
1 8001 1 教师1
2 8002 1 教师2
3 8003 2 教师3
4 8004 2 教师4
5 8005 3 教师5
(
Select distinct(teacher_id) AS teacher_id,count(*) AS count from class group by teacher_id
) AS R
Inner join base AS B
On B.teacher_id = R.teacher_id
Inner joib depart AS D
On D.department_id = B.department_id
depart:部门表
class:签到表
create procedure pro_join
as
select t.id,teacher_name,depart_name,sum(q.teacher_id)
from teacher t,department d,qiandao q
where t.teacher_id=q.teacher_id and t.department_id=q.department_id