SQL从两个表中获取最大日期

发布于 2024-08-12 16:33:55 字数 471 浏览 1 评论 0原文

我有两个表，

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

我想从两个表中获取用户详细信息和最新日期：

johns,John,Smith,2009-07-01
andrews,Andrew,Stiller,2009-05-05

帮助？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

吹梦到西洲 2024-08-19 16:33:56

对于那些需要简单直接选择的人：

select max(lastmodified) 
from (
    select max(lastmodified) as lastmodified from USER 
    union 
    select max(lastmodified) as lastmodified from STUDIES
);

这将从 USER 获取最大日期，然后从 STUDIES 获取最大日期，然后返回这 2 个中的最大值。此外，您可能需要向内部选择添加 where 子句和一些条件以改善结果。

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.

回复收藏 0 原文

陌伤浅笑 2024-08-19 16:33:56

像这样的东西

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

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

回复收藏 0 原文

通知家属抬走 2024-08-19 16:33:56

SELECT MAX(Date) FROM Users u FULL JOIN Studies s ON u.Username=s.Username GROUP BY Username

SELECT MAX(Date) FROM Users u FULL JOIN Studies s ON u.Username=s.Username GROUP BY Username

回复收藏 0 原文

方圜几里 2024-08-19 16:33:56

选择
MAX(更新日期) 为最新更新
从
（
从音频中选择更新日期
联合所有
从视频中选择更新日期
) 富

回复收藏 0 原文

漫雪独思 2024-08-19 16:33:55

您需要在这里组合 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 - 用于聚合，GREATEST - 用于两个值的最大值。

You need combination of MAX and GREATEST functions here:

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.

回复收藏 0 原文

~没有更多了~