begin
select
r1.startStop as startStop,
r1.routeName as route1,
r1.endStop as transitStop1,
r2.routeName as route2,
r2.endStop as transitStop2,
r3.routeName as route3,
r3.endStop as endStop,
r1.stopCount+r2.stopCount+r3.stopCount as stopCount
from
routeT0 r1,
routeT0 r2,
routeT0 r3
where
r1.startStop=sStop
and r1.endStop=r2.startStop
and r2.endStop=r3.startStop
and r3.endStop=eStop order by stopCount asc;
select
r1.startStop as startStop,
r1.routeName as route1,
r1.endStop as transitStop1,
r2.routeName as route2,
r2.endStop as transitStop2,
r3.routeName as route3,
r3.endStop as endStop,
r1.stopCount+r2.stopCount+r3.stopCount as stopCount
from
routeT0 r1,
routeT0 r2,
routeT0 r3
where
r1.startStop=sStop
and r1.endStop=r2.startStop
and r2.endStop=r3.startStop
and r3.endStop=eStop order by stopCount asc;
end
在存储过程的最后加个order by stopCount asc;
本文介绍了一个具体的SQL查询案例,展示了如何通过合理连接多个表并利用聚合函数来优化查询效率。通过对不同表之间的关联进行精确匹配,并按特定字段排序,使得查询结果既准确又高效。
2547

被折叠的 条评论
为什么被折叠?



