没有做不到,只有想不到:select * from( select a.schoolname as orderlist,a.schoolname,b.studentid,b.studentname,b.othercol from school a inner join student b on a.sid=b.sid where not exists(select 1 from student where sid=b.sid and studentid<a.studentid) union all select a.schoolname,'',a.schoolname,b.studentid,b.studentname,b.othercol from school a inner join student b on a.sid=b.sid where exists(select 1 from student where sid=b.sid and studentid>a.studentid) )t order by schoolname,studentid想想看,这样查询会得到什么样的结果.
改一下,主查询不显示第一列: select schoolname,studentid,studentname,othercol from( select a.schoolname as orderlist,a.schoolname,b.studentid,b.studentname,b.othercol from school a inner join student b on a.sid=b.sid where not exists(select 1 from student where sid=b.sid and studentid<a.studentid) union all select a.schoolname,'',a.schoolname,b.studentid,b.studentname,b.othercol from school a inner join student b on a.sid=b.sid where exists(select 1 from student where sid=b.sid and studentid>a.studentid) )t order by orderlist,studentid
想在程序里一次性展现出来。
我感觉可以这样做。
定义一个HashTable,把schoolId作为key,List<student>作为value
这样查出来以后,整个HashTable就是school的list
HashTable的value就是每个school的student的list
select a.schoolname as orderlist,a.schoolname,b.studentid,b.studentname,b.othercol
from school a inner join student b on a.sid=b.sid
where not exists(select 1 from student where sid=b.sid and studentid<a.studentid)
union all
select a.schoolname,'',a.schoolname,b.studentid,b.studentname,b.othercol
from school a inner join student b on a.sid=b.sid
where exists(select 1 from student where sid=b.sid and studentid>a.studentid)
)t order by schoolname,studentid想想看,这样查询会得到什么样的结果.
select schoolname,studentid,studentname,othercol from(
select a.schoolname as orderlist,a.schoolname,b.studentid,b.studentname,b.othercol
from school a inner join student b on a.sid=b.sid
where not exists(select 1 from student where sid=b.sid and studentid<a.studentid)
union all
select a.schoolname,'',a.schoolname,b.studentid,b.studentname,b.othercol
from school a inner join student b on a.sid=b.sid
where exists(select 1 from student where sid=b.sid and studentid>a.studentid)
)t order by orderlist,studentid