mysq连接两张表的查询,并limit其中一张表,怎么写

如我一次查完,可以这么写
select

• from class left join student on student.id = class.id 我想查询前30个班级的前10个学生,信息怎么搞 难道要查询31次, 1.查询有哪些班级,limit 30 2.分别查出每个班级的前10个学生 , limit 10

为什么一定要局限于在数据库上做？

我的建议是用程序控制，

如：
[code="java"]
StringBuffer sql = new StringBuffer("");

for(int i=0;i<count;i++){

sql.append("...");

if(i!=count-1){
sql.append(" union all");
}

}
[/code]

这样不就行了么？不就是一条语句进行执行了？

我只是提供一个思路，，，楼主，祝你好运。。。。

[code="sql"]
--oracle每个班级的前10个学生
select * from (select s.* ,ROW_NUMBER()OVER(PARTITION BY
s.id)AS row_id form student s ,class c where s.id=c.id)
where row_id =10;
[/code]

select nstudent.*
from
(select id cid
from
class
limit 30) nclass,
(select *
from
student
limit 10) nstudent
where nclass.cid = nstudent.classid
啊~~不知道这样可以么？

select * from student where id in(select rs.id from(select a.id from class a inner join student b on a.id = b.id order by a.id limit 30) as rs) order by id limit 10;

select * from student s group by s.class_id having s.class_id in (select c.class_id from class c limit 30 order by c.id) limit 10 order by s.id
按照班级分组，去班级的前三十然后每个班中取学生前十

好像上面的几个答案 都可以吧，你们认为呢？

有道理有道理有道理有道理

难道你的结果不是要10条，这个可以根据实际需求改的嘛。

多几种思路也不错

[code="java"]
SELECT *
FROM (SELECT * FROM t_class c WHERE rownum <= 30) c
LEFT JOIN (SELECT t1.*
FROM (SELECT t.*, rownum urownum FROM t_student t) t1
LEFT JOIN (SELECT t.fclassid, MIN(urownum) umin
FROM (SELECT t.*, rownum urownum FROM t_student t) t
GROUP BY t.fclassid) t2 ON t1.fclassid = t2.fclassid
WHERE t1.urownum < t2.umin + 10) t ON t.fclassid = c.fid
ORDER BY c.fid, t.fid;
[/code]

[code="sql"]
1.把班级取出前30

2.把学生都按班级（可加其他条件）排序并编上号
3.求出2中的每个班最小的号
4.用2跟3做关联，条件为2的班级等于3的班级，且2.编码小于3.编号+10

5.用1跟4做关联，条件为1的班级等于4的班级
[/code]

[code="sql"]
SELECT *
FROM (SELECT * FROM t_class c WHERE rownum <= 30) c
LEFT JOIN (SELECT t1.*
FROM (SELECT t.*, rownum urownum FROM (SELECT t.* FROM t_student t ORDER BY t.fclassid, t.fcj) t) t1
LEFT JOIN (SELECT t.fclassid, MIN(urownum) umin
FROM ((SELECT t.*, rownum urownum
FROM (SELECT t.* FROM t_student t ORDER BY t.fclassid, t.fcj) t)) t
GROUP BY t.fclassid) t2 ON t1.fclassid = t2.fclassid
WHERE t1.urownum < t2.umin + 10) t ON t.fclassid = c.fid
ORDER BY c.fid, t.fid;
[/code]