比赛项目表item(item_id, item_name,item_address)
比赛选手表sportsman(spor_id, item_name)
比赛成绩表grade(item_id, spor_id, grade)
查询:
找出在比赛各项目名称及其冠军的姓名
找出在(清华附中)比赛的各项目名称及其冠军的姓名
谢谢,读表可能有点麻烦,谢谢
比赛选手表sportsman(spor_id, item_name)
比赛成绩表grade(item_id, spor_id, grade)
查询:
找出在比赛各项目名称及其冠军的姓名
找出在(清华附中)比赛的各项目名称及其冠军的姓名
谢谢,读表可能有点麻烦,谢谢
--找出在比赛各项目名称及其冠军的姓名
select a.item_name,b.item_name.c.grade
from item a,sportsman b,grade,c
where a.item_id=c.item_id and b.spor_id=c.spor_id
and not exists
(select 1 from grade
where grade.spor_id=c.spor_id
and spor_id.grade>c.grade)
--找出在(清华附中)比赛的各项目名称及其冠军的姓名
select a.item_name,b.item_name.c.grade
from item a,sportsman b,grade,c
where a.item_id=c.item_id and b.spor_id=c.spor_id
and a.item_address='清华附中'
and not exists
(select 1 from item,grade
where item.item_id=grade.item_id
and grade.spor_id=c.spor_id and item_address='清华附中'
and spor_id.grade>c.grade )
无法绑定由多个部分组成的标识符 "spor_id.grade"。
我用sql2005执行的
--找出在比赛各项目名称及其冠军的姓名
select a.item_name,b.item_name.c.grade
from item a,sportsman b,grade c
where a.item_id=c.item_id and b.spor_id=c.spor_id
and not exists
(select 1 from grade d
where d.item_id=c.item_id
and d.grade>c.grade)
--找出在(清华附中)比赛的各项目名称及其冠军的姓名
select a.item_name,b.item_name.c.grade
from item a,sportsman b,grade,c
where a.item_id=c.item_id and b.spor_id=c.spor_id
and a.item_address='清华附中'
and not exists
(select 1 from item,grade
where item.item_id=grade.item_id
and grade.item_id=c.item_id and item_address='清华附中'
and grade.grade>c.grade )