比如查询学号为2014XXX的学生考的最好的科目和成绩 select * from stu where no like '2014%' 程序遍历结果然后: select * from sub where sub.stuid=stu.id order by score desc limit 1; 这样连接就比较容易,但是如何拼成一个SQL语句呢
select * from sub a inner join ( select * from stu where no like '2014%' ) b on a.stuid=b.id order by score desc limit 1;
比如查询学号为2014XXX的学生考的最好的科目和成绩 select * from stu where no like '2014%' 程序遍历结果然后: select * from sub where sub.stuid=stu.id order by score desc limit 1; 这样连接就比较容易,但是如何拼成一个SQL语句呢 SELECT t1.* FROM sub t1, (SELECT * FROM stu WHERE NO LIKE '2014%' ) t2 WHERE t1.stuid=t2.id ORDER BY t1.score DESC
问题要讲清楚 假设stu表中有score字段 select * from sub a inner join stu a1 on a.stuid=a1.id whre not exists(select 1 from stu where a1.id=id and a1.score<score)
这个貌似只能查出一条记录吧? 如果是每个学生都要呢?去掉limit即可。
select * from sub s where stuid in ( select id from stu where no like '2014%' ) and not exists (select 1 from sub where stuid=s.stuid and score>s.score)
比如查询学号为2014XXX的学生考的最好的科目和成绩
select * from stu where no like '2014%'
程序遍历结果然后:
select * from sub where sub.stuid=stu.id order by score desc limit 1;
这样连接就比较容易,但是如何拼成一个SQL语句呢
( select * from stu where no like '2014%' ) b
on a.stuid=b.id order by score desc limit 1;
比如查询学号为2014XXX的学生考的最好的科目和成绩
select * from stu where no like '2014%'
程序遍历结果然后:
select * from sub where sub.stuid=stu.id order by score desc limit 1;
这样连接就比较容易,但是如何拼成一个SQL语句呢 SELECT t1.* FROM sub t1, (SELECT * FROM stu WHERE NO LIKE '2014%' ) t2
WHERE t1.stuid=t2.id ORDER BY t1.score DESC
假设stu表中有score字段
select * from sub a inner join stu a1
on a.stuid=a1.id
whre not exists(select 1 from stu where a1.id=id and a1.score<score)
这个貌似只能查出一条记录吧? 如果是每个学生都要呢?去掉limit即可。
select *
from sub s
where stuid in ( select id from stu where no like '2014%' )
and not exists (select 1 from sub where stuid=s.stuid and score>s.score)