oracle SQL从两个表中获取最大日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1789919/
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
SQL getting max date from two tables
提问by Egor Rogov
I have two tables
我有两张桌子
USER (one row per user)
id,username,firstname,lastname,lastmodified
1,johns, John,Smith, 2009-03-01
2,andrews, Andrew,Stiller, 2009-03-03
STUDIES (multiple rows per user)
id,username,lastmodified
1,johns, 2009-01-01
1,johns, 2009-02-01
1,johns, 2009-07-01
2,andrews,2009-05-05
2,andrews,2009-04-04
I want to get users details and the NEWEST date from the two tables:
我想从两个表中获取用户详细信息和最新日期：
johns,John,Smith,2009-07-01
andrews,Andrew,Stiller,2009-05-05
Help?
帮助？
回答by Egor Rogov
You need combination of MAX and GREATEST functions here:
您需要在此处结合使用 MAX 和 GREATEST 功能：
select u.username
, u.firstname
, u.lastname
, greatest(u.lastmodified,max(s.lastmodified))
from USER u
, STUDIES s
where s.id = u.id
group by u.id
, u.username
, u.firstname
, u.lastname
, u.lastmodified
MAX -- for aggregation, GREATEST -- for maximum of two values.
MAX -- 用于聚合，GREATEST -- 最多两个值。
回答by Seth Moore
SELECT MAX(Date) FROM Users u FULL JOIN Studies s ON u.Username=s.Username GROUP BY Username
回答by demas
Something like this
像这样的东西
select username, max(lastmodified) from (
select username, lastmodified from user
union all
select username, max(lastmodified) as lastmodified
from studies
group by username
) s
group by username
回答by pXel
For those who need a simple straightforward select:
对于那些需要简单直接选择的人：
select max(lastmodified)
from (
select max(lastmodified) as lastmodified from USER
union
select max(lastmodified) as lastmodified from STUDIES
);
This will get the max date from USER then the max date from STUDIES, and then it will return the max of those 2. Also you may want to add where clause and some conditions to the inner selections in order to improve the result.
这将从 USER 获得最大日期，然后从 STUDIES 获得最大日期，然后它将返回这 2 个的最大值。此外，您可能希望将 where 子句和一些条件添加到内部选择中以改善结果。
回答by Vinayak Maradkar
SELECT MAX(updatedDate) as latestUpdated FROM ( SELECT updatedDate FROM audio UNION ALL SELECT updatedDate FROM videos )foo
SELECT MAX(updatedDate) as latestUpdated FROM ( SELECT updatedDate FROM audio UNION ALL SELECT updatedDate FROM videos )foo
