select a.Badge,a.Name,a.Department,b.Course,max(b.Grade) from Employee a,Training b where a.badge=b.badge group by a.Badge,a.Name,a.Department,b.Course
1: select Badge、Name、Department、Course、Grade from Employee,Training a where Employee.Badge = a.Badge and a.Grade = (select max(Grade) from Training b where b.Course = a.Course);
1. selecr t.badge,t.name,t.department,tt.course,tt.grade from Employee t,(select ta.badge badge,ta.course course,ta.grade grade from training ta,(select a.course course,max(a.grade) max_grade from training a group by a.course) where ta.course=a.course and ta.grade=a.max_grade) tt where t.badge=tt.badge
1: select a.badge,a.name,a.Department,b.Course,b.Grade from employee a,training b where a.badge = b.badge and b.grade in (select max(grade) from training c where b.course = c.course group by course)
insert into Employee(Badge) (select Badge from Training where badge not in(select Badge from Employee ))
2. insert into employee(badge) select a.badge from training a where a.badge not in (select b.badge from employee b)
2: insert into Employee select Badge from Training where exists(select * from Employee where Employee.Badge = Training.Badge);
写错!!!! 2: insert into Employee select Badge from Training where not exists(select * from Employee where Employee.Badge = Training.Badge);
2: insert into employee(badge) select badge from training b where not exists(select * from employee a where a.badge = b.badge) 吃饭去来,来晚了
zhpsam109(孤寂无边) 兄也在啊
落雪山林的好象有点问题 如果有一课比如说是A吧它最高成绩是12分 ,那么vb就有会被选中两次
未测试: select distinct e.Badge,e.Name, e.Department,t.Course,t.Grade from Employee e, Training t,(select Badge ,max(Grade) as grade from Training group by Badge) a where e.Badge=t.badge and e.Badge=a.badge and t.badge=a.badge
select Badge、Name、Department、Course、Grade
from Employee,Training a
where Employee.Badge = a.Badge
and a.Grade =
(select max(Grade) from Training b where b.Course = a.Course);
selecr t.badge,t.name,t.department,tt.course,tt.grade from Employee t,(select ta.badge badge,ta.course course,ta.grade grade from training ta,(select a.course course,max(a.grade) max_grade from training a group by a.course) where ta.course=a.course and ta.grade=a.max_grade) tt where t.badge=tt.badge
select a.badge,a.name,a.Department,b.Course,b.Grade from employee a,training b where a.badge = b.badge and
b.grade in (select max(grade) from training c where b.course = c.course group by course)
insert into employee(badge) select a.badge from training a where a.badge not in (select b.badge from employee b)
insert into Employee select Badge from Training where exists(select * from Employee where Employee.Badge = Training.Badge);
2:
insert into Employee select Badge from Training where not exists(select * from Employee where Employee.Badge = Training.Badge);
insert into employee(badge) select badge from training b where not exists(select * from employee a where a.badge = b.badge)
吃饭去来,来晚了
select distinct e.Badge,e.Name,
e.Department,t.Course,t.Grade
from Employee e, Training t,(select Badge ,max(Grade) as grade
from Training
group by Badge) a
where e.Badge=t.badge
and e.Badge=a.badge
and t.badge=a.badge