mysql - 生成查询以连接不同的记录

Question

sql/hive的新手...

 SELECT id,seed, day FROM  table_1 WHERE  day = to_date('2016-06-09') limit 5;
302766500   R388899 2016-06-09
692010468   R61140  2016-06-09
662084962   R165803 2016-06-09
1818260515  R411276 2016-06-09
646246322   R426737 2016-06-09


SELECT id, exp, day FROM table_2 WHERE day = to_date('2016-06-09')  limit 5;
OK
2595    e137_1  2016-06-09
2595    e137_2  2016-06-09
4372    e137_1  2016-06-09
7256    e137_1  2016-06-09
18674   e137_1  2016-06-09
Time taken: 1.475 seconds, Fetched: 5 row(s)

如您在表 2 中所见.. listener id 重复 (2595)..

因此，我想从表 2 中找出两个日期范围之间的不同 ID

然后在给定日期范围内从表 1 中找到这些 ID 的种子。

SELECT id, seed FROM table_1 WHERE day = to_date('some date')
JOIN (
SELECT DISTINCT id FROM table_2 WHERE day = to_date('some_date')
) l
ON l.id = id;

但是我得到这个错误:

ParseException line 3:0 missing EOF at 'JOIN' near ')'

Answer 1

好像位置不对

SELECT table_1.id, table_1.seed FROM table_1 
JOIN (
SELECT DISTINCT id FROM table_2 WHERE day = to_date('some_date')
) l
ON l.id = table_1.id
WHERE table_1.day = to_date('some date')

;