Oracle sql 内连接右表中的第一条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20085934/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle sql Inner join first record in right table
提问by phouse512
my question is this:
我的问题是：
I have two tables such as this:
我有两张这样的表：
username | portname | symbol | shares
---------+----------+--------+-------
phil | test | APL | 214
---------+----------+--------+--------
It has more records, but that's just an example. Then I have another table such as this, that has multiple records per symbol
它有更多的记录，但这只是一个例子。然后我有另一个像这样的表，每个符号有多个记录
symbol | high | low | timestamp
-------+------+-----+-----------
APL | 200 | 20 | *timestamp object
APL | 400 | 34 | *timestamp object
I want a table to be returned where I join the two, but only the first row from the second table is joined so something like this is returned:
我希望在我加入两个表的地方返回一个表，但只有第二个表中的第一行被加入，因此返回如下内容：
symbol | high | low | timestamp
-------+------+-----+----------
APL | 400 | 34 | *timestamp object
So only one record from the right table is matched. I've tried alot of things but haven't gotten anything to work with group by's or distinct.
所以只有右表中的一条记录匹配。我已经尝试了很多东西，但没有得到任何与 group by 或不同的东西一起工作。
Thanks!
谢谢！
回答by PM 77-1
SELECT t1.symbol, t3.high, t3.low, t3.timestamp
FROM Table1 t1
JOIN (
SELECT inn.*
FROM (SELECT t2.*, (ROW_NUMBER() OVER(PARTITION BY symbol ORDER BY timestamp DESC)) As Rank
FROM Table2 t2) inn
WHERE inn.Rank=1
) t3
ON t1.symbol = t3.symbol;
See SQL Fiddle
请参阅SQL 小提琴
