怎么写这个sql语句?

[huang_2]

假设我现在想挑出符合以下条件的 MtlineId 的值,要求这个值的StationID字段同时有 123 和 456两个值

表结构如下:


Id MtLineId StationId

21225 2 744
21227 2 104
21228 2 775
21229 2 1178
21231 2 113
21232 2 909
21233 2 759
21237 3 170
21238 3 1170
21239 3 562
21240 3 105
21241 3 655
21243 3 39
21244 3 636
21246 4 15
21248 4 143
21250 4 1180
21252 4 1171
21254 4 3
21255 4 1030
21256 4 743
21258 4 700
21259 4 940

[Yang_]

select distinct a.MtLineId
from tablename a,tablename b
where a.StationId=b.StationId
and a.id<>b.id

[Yang_]

上面错了：

select distinct a.MtLineId
from tablename a,tablename b
where a.StationId=123
and b.b.StationId=456
and a,MtLineId=b.MtLineId
and a.id<>b.id

[bowlder]

应该这样就可以了吧。
select distinct MtLineId from tablename where StationID=123 or StationID=456

[huang_2]

i test it

[Yang_]

select distinct a.MtLineId
from tablename a,tablename b
where a.StationId=123
and b.StationId=456
and a.MtLineId=b.MtLineId
and a.id<>b.id

[Yang_]

select distinct a.MtLineId
from tablename a,tablename b
where a.StationId=123
and b.StationId=456
and a.MtLineId=b.MtLineId

[huang_2]

我只有一个表,怎么变了两个表?

[Yang_]

自连接

[pcdreama]

呵呵，学到东西了！

[pqds]

同意自连接

[hillhx]

haha

[supsuccess]

select t1.MtLineId from
(select distinct MtLineId from tab where StationId=123)t1 join
(select distinct MtLineId from tab where StationId=456)t2 on t1.MtLineId=t2.MtLineId