SELECT i.itemname,s.name FROM grade g,
(SELECT itemid iid,MAX(mark) max FROM grade WHERE itemid IN ( SELECT itemid FROM item WHERE location='体育馆') GROUP BY itemid) temp,item i,sporter s
WHERE g.itemid=temp.iid AND g.mark=temp.max AND temp.iid=i.itemid AND s.sporterid=g.sporterid;
SELECT i.itemname,s.name FROM grade g,
(SELECT itemid iid,MIN(mark) max FROM grade WHERE itemid IN ( SELECT itemid FROM item WHERE location='体育馆') GROUP BY itemid) temp,item i,sporter s
WHERE g.itemid=temp.iid AND g.mark=temp.max AND temp.iid=i.itemid AND s.sporterid=g.sporterid;
SELECT i.itemname,s.name FROM grade g,
(SELECT itemid iid,MAX(mark) max FROM grade WHERE itemid IN ( SELECT itemid FROM item WHERE location='体育馆') ) temp,item i,sporter s
WHERE g.itemid=temp.iid AND g.mark=temp.max AND temp.iid=i.itemid AND s.sporterid=g.sporterid;
SELECT i.itemname,s.name FROM grade g,
(SELECT itemid iid,MIN(mark) max FROM grade WHERE itemid IN ( SELECT itemid FROM item WHERE location='体育馆') GROUP BY itemid) temp,item i,sporter s
WHERE g.itemid=temp.iid AND g.mark=temp.max AND
1、先找到体育馆的所有项目id: SELECT itemid FROM item WHERE location='体育馆' = xid(假设是xid)
2、再找到所有项目对应的最高分: SELECT itemid iid ,MAX(mark) max FROM grade WHERE itemid IN (xid…) GROUP BY itemid = Temp
3、输出复合条件的结果: SELECT i.itemname,s.name FROM grade g,Temp temp,item i,sporter s WHERE g.itemid=temp.iid AND g.mark=temp.max AND temp.iid=i.itemid AND s.sporterid=g.sporterid;
SELECT i.itemname,s.name
FROM grade g,
(SELECT itemid iid,MAX(mark) max
FROM grade
WHERE itemid IN (
SELECT itemid
FROM item
WHERE location='体育馆')
GROUP BY itemid) as temp
temp,item i,sporter s
WHERE g.itemid=temp.iid AND g.mark=temp.max AND temp.iid=i.itemid AND s.sporterid=g.sporterid;
分段看就明白了。先写出一个含有最高成绩表temp(对项目进行分组求最高分,不会单独显示运动员id了,因此之后要联结这个原grade表才能有运动员id才能再联结sporter来确定name):
(select itemid iid,max(mark) max from grade where itemid in(select itemid from item where location=’体育馆’ ) group by itemid) temp;
要根据最高成绩表temp中itemid→联结项目表item得到itemname
原成绩表grade中运动员id→联结运动员表sporter得到姓名name
因此,是要把temp与其他3个表联结起来:
Select i.itemname,s.name from grade g,(select itemid iid,max(mark) max from grade where itemid in(select itemid from item where location=’体育馆’ ) temp,item i,sporter s where g.itemid=temp.iid and g.mark=temp.max and temp.itemid=i.itemid and s.sporterid=g.id;
SELECT i.itemname, s.name FROM grade g, (SELECT itemid iid, MAX(mark) max FROM grade WHERE itemid IN (SELECT itemid FROM item WHERE location = '体育馆&(5597)#39;) GROUP BY itemid) temp, item i, sporter s WHERE g.itemid = temp.iid AND g.mark = temp.max AND temp.iid = i.itemid AND s.sporterid = g.sporterid ;
结构:
1:查出 在体育馆 比赛,含有冠军信息的项目。
2:四表 进行一个 联立,查出信息。
总觉得 还可以优化,来个 高手优化下。
//要查询的信息 : 项目的名称, 运动员的姓名 SELECT i.itemname, s.name //要查询的表 : 1. 包含项目编号和最高积分的表 temp // 2. 项目表 item i // 3. 运动员表 sporter s FROM grade AS g, (SELECT itemid AS iid, MAX(mark) AS max_socre FROM grade WHERE itemid IN (SELECT itemid FROM item WHERE location='体育馆') GROUP BY itemid) AS temp, item AS i, sporter s //条件判断 WHERE g.itemid = temp.iid //成绩表的项目编号itemid = temp表的项目编号iid AND g.mark = temp.max_score //成绩表的积分 = temp表的最高分 AND temp.iid = i.itemid //temp表的项目编号 = 项目表的itemid AND s.sporterid = g.id //运动员表的运动员编号sporterid = 成绩表的运动员编号id