自己想出来了,还得继续努力学习啊
select a.train from (select train from passstation where station='zj') as a where a.train in (select train from passstation where station='nj');
select a.train from (select train from passstation where station='zj') as a where a.train in (select train from passstation where station='nj');
应该还有其他方法....
WITH T AS(
SELECT 1 AS ID,110 AS TRAIN ,1 AS ORD ,'SH' AS STATION FROM DUAL UNION
SELECT 2, 110, 2, 'NJ' FROM DUAL UNION
SELECT 3, 112, 1, 'BJ' FROM DUAL UNION
SELECT 4, 112, 2, 'ZJ' FROM DUAL UNION
SELECT 5, 110, 3, 'JS' FROM DUAL UNION
SELECT 6, 110, 4, 'ZJ' FROM DUAL
)
--第四种方法
SELECT DISTINCT A.TRAIN
FROM T A, T B
WHERE A.STATION IN ('NJ', 'ZJ')
AND B.STATION IN ('NJ', 'ZJ')
AND A.TRAIN = B.TRAIN
AND A.STATION != B.STATION;
--第三种方式
SELECT TRAIN FROM(
SELECT TRAIN,WMSYS.WM_CONCAT(STATION) AS STATIONS FROM T GROUP BY TRAIN
) O
WHERE INSTR(STATIONS,'NJ')>0 AND INSTR(STATIONS,'ZJ')>0;
--第二种方式
SELECT TRAIN FROM T WHERE T.STATION = 'NJ' AND TRAIN IN (SELECT TRAIN FROM T A WHERE A.STATION='ZJ');
--第一种方式
SELECT TRAIN FROM T WHERE T.STATION = 'NJ' AND EXISTS(SELECT 1 FROM T A WHERE A.TRAIN = TRAIN AND A.STATION='ZJ');
mysql> select train from test where station='zj' or station='nj'
-> group by train having count(*)=2;
+-------+
| train |
+-------+
| 110 |
+-------+
1 row in set (0.00 sec)