Oracle SQL 查询 - 使用 MAX() 函数时加入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32551753/
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 Query - Join when using MAX() function
提问by jamop89
Good afternoon,
下午好，
I am trying to put together a query that will select data from 2 tables when using the MAX() function to return the user's most recent login time.
我正在尝试组合一个查询，当使用 MAX() 函数返回用户最近的登录时间时，该查询将从 2 个表中选择数据。
The tables are as follows:
表格如下：
USERS:
用户：
USERNAME CREATED
JOHNSMITH 01/01/2015
MATTTYLER 12/12/2013
DAVIDCROSS 09/07/2014
SARAHTHOMPSON 02/05/2015
SESSIONS:
课程：
USERNAME ACTION TIMESTAMP
JOHNSMITH LOGOUT 13/09/2015 10:00:00
MATTTYLER LOGOUT 13/09/2015 05:00:00
JOHNSMITH LOGIN 12/09/2015 15:00:00
MATTTYLER LOGIN 12/09/2015 11:00:00
JOHNSMITH LOGOUT 12/09/2915 12:00:00
JOHNSMITH LOGIN 12/09/2015 05:00:00
Result:
结果：
USERNAME CREATED TIMESTAMP (as LASTLOGIN)
JOHNSMITH 01/01/2015 12/09/15 15:00:00
MATTTYLER 12/12/2013 12/09/15 11:00:00
DAVIDCROSS 09/07/2014 NULL
SARAHTHOMPSON 02/05/2015 NULL
If MAX() is not the most appropriate function to select the most recent login time, please feel free to suggest a better approach. If possible, can you please demonstrate how to achieve this using both the SQL-89 and SQL-92 joins?
如果 MAX() 不是选择最近登录时间的最合适的函数，请随时提出更好的方法。如果可能，您能否演示如何使用 SQL-89 和 SQL-92 连接来实现这一点？
Any help is greatly appreciated, thank you.
非常感谢任何帮助，谢谢。
回答by jarlh
Do a LEFT JOINto also get users never logged in. GROUP BYwith MAXto get each user's latest TIMESTAMP.
做一个LEFT JOIN也得到了用户从未登录。GROUP BY以MAX获得每个用户的最新TIMESTAMP。
select u.username, u.created, max(s."TIMESTAMP")
from users u
left join sessions s on u.username = s.username
group by u.username, u.created
SQL-92 (join used. Timestampis a reserved word in SQL-92, that's why it's delimited as "TIMESTAMP".
SQL-92（连接使用。Timestamp是 SQL-92 中的保留字，这就是为什么它被分隔为"TIMESTAMP".
SQL-89 (aka SQL 1), if no left join available, do a correlated sub-query instead:
SQL-89（又名 SQL 1），如果没有可用的左连接，则改为执行相关子查询：
select u.username, u.created,
(select max("TIMESTAMP") from sessions s
where u.username = s.username)
from users u
(Didn't Oracle have +=or =+for left join, when old-style join syntax is used?)
（当使用旧式连接语法时，Oracle 没有+=或=+用于左连接吗？）
回答by Brian DeMilia
Another way to do this is via analytic functions:
另一种方法是通过分析函数：
select username,
created,
"TIMESTAMP"
from (select u.*,
s."TIMESTAMP",
row_number() over( partition by u.username
order by s."TIMESTAMP" desc ) as rnk
from users u
left join sessions s
on u.username = s.username)
where rnk = 1
回答by TommCatt
Using a group bywith maxis fine when you are looking only at a few fields, but if you wanted, say, many fields from Users and/or Sessions along with just the last login date added, the group bylist could be really long. Here is an alternate method that doesn't use grouping.
当您只查看几个字段时，使用group bywithmax很好，但是如果您想要，比如说，来自用户和/或会话的许多字段以及添加的最后登录日期，group by列表可能会很长。这是一种不使用分组的替代方法。
What this does is take each row from Users and join it with the Session row with the login time from the one correlated row from Sessions with the latest login time.
这样做是从用户中获取每一行，并将其与会话行连接，登录时间来自会话中具有最新登录时间的相关行。
select u.*, s.Timestamp "Last Login"
from Users u
left join Sessions s
on s.Username = u.Username
and s.Action = 'login'
and s.Timestamp =(
select Max( timestamp )
from Sessions
where UserName = s.Username
and Action = s.Action );
The natural key of Sessions is (Username, Timestamp) but if there is an index on (UserName, Action, Timestamp), the query above will run impressively fast even though it has a subquery.
Sessions 的自然键是 (Username, Timestamp) 但如果 (UserName, Action, Timestamp) 上有索引，上面的查询将运行得非常快，即使它有一个子查询。
Notice, btw, that you're looking for the lastest logintimestamp, not just the latest timestamp.
请注意，顺便说一句，您正在寻找最新的登录时间戳，而不仅仅是最新的时间戳。
However, the query above, while it will work on most DBMSs, does not work with Oracle. It has a "feature" that does not allow subqueries in outer joins. This is inconvenient but not insurmountable. Old Oracle hands probably already know what to do, but here it is for the everyone else.
然而，上面的查询虽然适用于大多数 DBMS，但不适用于 Oracle。它有一个“功能”，不允许在外部联接中使用子查询。这很不方便，但并非不可克服。老 Oracle 手可能已经知道该怎么做，但这里是为其他人准备的。
We have to move the subquery from the join criteria to the filter criteria. Normally, this is just a matter of cutting and pasting, or in this case, just change the final andto where:
我们必须将子查询从连接条件移动到过滤条件。通常，这只是剪切和粘贴的问题，或者在这种情况下，只需将 final 更改and为where：
select u.*, s.Timestamp "Last Login"
from Users u
left join Sessions s
on s.Username = u.Username
and s.Action = 'login'
where s.Timestamp =(
select Max( timestamp )
from Sessions
where UserName = s.Username
and Action = s.Action );
However, when you move some or all of the join criteria of an outer join to the filtering clause, you now filter out the results where the Sessions data is null -- thus making the result set the same as an inner join. Many mistakenly think the outer join is actually converted to an inner join, but it's just the output is filtered so it looks like an inner join.
但是，当您将外部连接的部分或全部连接条件移至过滤子句时，您现在会过滤掉会话数据为空的结果——从而使结果集与内部连接相同。许多人错误地认为外连接实际上被转换为内连接，但它只是输出被过滤，所以它看起来像内连接。
This is also easily fixed. Just insert an explicit filter for those null results:
这也很容易解决。只需为那些空结果插入一个显式过滤器：
select u.*, s.Timestamp "Last Login"
from Users u
left join Sessions s
on s.Username = u.Username
and s.Action = 'login'
where s.Username is null
or s.Timestamp =(
select Max( timestamp )
from Sessions
where UserName = s.Username
and Action = s.Action );
Ok, because this is Oracle, a fairly simple answer is a little more complicated. But it's good to know alternate methods.
好的，因为这是 Oracle，所以一个相当简单的答案会稍微复杂一些。但是了解替代方法是很好的。
And no, I don't know how to do this using the old join syntax. I'm sure there's a way, I just moved over as soon as the new syntax hit and never went back. So that's been a looooooooong time for me.
不，我不知道如何使用旧的连接语法来做到这一点。我确定有一种方法，一旦新语法出现，我就搬过来，再也没有回去。所以这对我来说是一个很长的时间。
