不知这个sql还能不能进一步优化

Koson

[code=sql]SELECT b1.title_sc AS origin ,b1.lineid ,b2.title_sc AS destination,b2.stoporder-b1.stoporder AS stopcount FROM gw_bus_stop b1 inner join
gw_bus_stop b2 on b1.lineid=b2.lineid AND b1.stoporder<b2.stoporder AND b1.title_sc='牛头角' AND b2.title_sc='利景'[/code]

站长苏飞

首先你的语句都不对吧
[code=sql]    SELECT b1.title_sc AS origin ,b1.lineid ,b2.title_sc AS destination,b2.stoporder-b1.stoporder AS stopcount FROM gw_bus_stop b1 inner join
    gw_bus_stop b2 on b1.lineid=b2.lineid AND b1.stoporder<b2.stoporder AND b1.title_sc='牛头角' AND b2.title_sc='利景'[/code]
b2 b1是同一个表b1.lineid=b2.lineid 其它条件应该都不会成立了吧比如 b1.title_sc='牛头角' AND b2.title_sc='利景'
title_sc这一个字段怎么会有两个值呢？
应该这样写吧
[code=sql]SELECT b1.title_sc AS origin ,b1.lineid ,b2.title_sc AS destination,b2.stoporder-b1.stoporder AS stopcount
FROM
gw_bus_stop b1 inner join
gw_bus_stop b2
on b1.lineid=b2.lineid
WHERE b1.stoporder<b2.stoporder AND b1.title_sc='牛头角' AND b2.title_sc='利景'[/code]
当然也可以这样写
[code=sql]SELECT b1.title_sc AS origin ,b1.lineid ,b2.title_sc AS destination,b2.stoporder-b1.stoporder AS stopcount
FROM
gw_bus_stop b1 ,
gw_bus_stop b2
WHERE b1.stoporder<b2.stoporder AND b1.title_sc='牛头角' AND b2.title_sc='利景'[/code]

Koson

sql语句没有问题的，其实是巴士的站点表。我的lineid字段不是pk，是巴士线路，一个lineid下多个title_sc,要查询的两个站点是否在同一条线路下。
sid lineid stoporder  title_sc       .....
1     1         1        站点一   
2     1         2        站点二
3     1         3        站点三            
你看我的这条sql语句能否查出站点一到站点三的结果来？
lineid＝1；stoporder>1；title_sc不同。
SELECT b1.title_sc AS origin ,b1.lineid ,b2.title_sc AS destination,b2.stoporder-b1.stoporder AS stopcount FROM gw_bus_stop b1 inner join
gw_bus_stop b2 on b1.lineid=b2.lineid AND b1.stoporder<b2.stoporder AND b1.title_sc='站点一' AND b2.title_sc='站点三'。
当然，或许是我的sql水平太低了。理解不对，希望苏飞大哥指正。谢谢。

站长苏飞

哦我以为lineid 是主键
你试试我的这个行不[code=sql]SELECT b1.title_sc AS origin ,b1.lineid ,b2.title_sc AS destination,b2.stoporder-b1.stoporder AS stopcount
FROM
gw_bus_stop b1 ,
gw_bus_stop b2
WHERE b1.stoporder<b2.stoporder AND b1.title_sc='牛头角' AND b2.title_sc='利景'[/code]
然后再看看那个快

站长苏飞

哦我以为lineid 是主键
你试试我的这个行不[code=sql]SELECT b1.title_sc AS origin ,b1.lineid ,b2.title_sc AS destination,b2.stoporder-b1.stoporder AS stopcount
FROM
gw_bus_stop b1 ,
gw_bus_stop b2
WHERE b1.stoporder<b2.stoporder AND b1.title_sc='牛头角' AND b2.title_sc='利景'[/code]
然后再看看那个快

Koson

admin 发表于 2013-1-9 10:13
哦我以为lineid 是主键
你试试我的这个行不
然后再看看那个快

行，我最开始就是使用的这个where，之后网上找找说where最终还是会被解释成join，所以就改了下，但基本差不多，可能是数据比较少吧。没什么体现。哈哈