我用MySQL模擬的一個表，其實Hive語法和SQL差不多，插入了三條數據，a, b, c 分別代表三個機場名稱，結構如下：
mysql> show create table t/G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `airport` varchar(10) DEFAULT NULL, `distant` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
mysql> select * from t; +---------+---------+ | airport | distant | +---------+---------+ | a | 130 | | b | 140 | | c | 150 | +---------+---------+ 3 rows in set (0.00 sec) 通過!=篩選掉本機場自己之間的比較，用abs函數取絕對值得到位置小于100的兩個機場
mysql> select t1.airport, t2.airport from t t1,t t2 where t1.airport != t2.airport and abs(t1.distant-t2.distant) < 100; +---------+---------+ | airport | airport | +---------+---------+ | b | a | | c | a | | a | b | | c | b | | a | c | | b | c | +---------+---------+ 6 rows in set (0.00 sec) 但是問題來了，(b,a) 與(a,b)，(c,a)與(a,c)，(c,b)與(b,c)這里被我們視為重復值，我們只需要得到其中某一行的數據，就知道是哪兩個機場名了，那么，如何去掉這個重復值呢？
mysql> select t1.airport,hex(t1.airport), t2.airport,hex(t2.airport) from t t1,t t2 where t1.airport != t2.airport and abs(t1.distant-t2.distant) < 100; +---------+-----------------+---------+-----------------+ | airport | hex(t1.airport) | airport | hex(t2.airport) | +---------+-----------------+---------+-----------------+ | b | 62 | a | 61 | | c | 63 | a | 61 | | a | 61 | b | 62 | | c | 63 | b | 62 | | a | 61 | c | 63 | | b | 62 | c | 63 | +---------+-----------------+---------+-----------------+ 6 rows in set (0.00 sec) 這樣我們就可以通過比較機場1和機場2的大小，來去掉重復值了
mysql> select t1.airport, t2.airport from t t1,t t2 where t1.airport != t2.airport and hex(t1.airport) < hex(t2.airport) and abs(t1.distant-t2.distant) < 100; +---------+---------+ | airport | airport | +---------+---------+ | a | b | | a | c | | b | c | +---------+---------+ 3 rows in set (0.00 sec) 最后再優化一下，結果如下：
mysql> select t1.airport, t2.airport from t t1,t t2 where hex(t1.airport) < hex(t2.airport) and abs(t1.distant-t2.distant) < 100; +---------+---------+ | airport | airport | +---------+---------+ | a | b | | a | c | | b | c | +---------+---------+ 3 rows in set (0.00 sec)
