如何使用SQL输出多列最新信息

发布于 2024-10-10 20:25:09 字数 470 浏览 4 评论 0 原文

我下面有一个包含 3 列的“天气”表：

 City Temperature Date
 New York    22 C 10/10/2005
 Seattle     21 C 10/10/2005
 New York    18 C 10/09/2005
 Seattle     20 C 10/09/2005
 Washington  17 C 10/09/2005
 New York    21 C 10/08/2005
 Washington  20 C 10/08/2005

我也想在 3 列中找到有关城市和温度的最新信息（参见示例）：

 City Temperature Date
 New York    22 C 10/10/2005
 Seattle     21 C 10/10/2005
 Washington  17 C 10/09/2005

有人可以帮忙吗？

原文

I have a "weather" table below with 3 cols:

 City Temperature Date
 New York    22 C 10/10/2005
 Seattle     21 C 10/10/2005
 New York    18 C 10/09/2005
 Seattle     20 C 10/09/2005
 Washington  17 C 10/09/2005
 New York    21 C 10/08/2005
 Washington  20 C 10/08/2005

I want to find out the latest info on the City and Temperature in 3 cols as well (see example):

 City Temperature Date
 New York    22 C 10/10/2005
 Seattle     21 C 10/10/2005
 Washington  17 C 10/09/2005

Can anyone help?

分享到QQ

分享到微博

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

发布评论

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

意中人 2024-10-17 20:25:09

在子查询中查找每个城市的最大（最新）日期，然后连接日期和城市：

select weather.* 
  from weather
  inner join    
    (select city, max(date) from weather group by city) as latest
    on weather.date = latest.date
      and weather.city = latest.city

Find the maximum (latest) date for each city in a sub-query then join on the date and city:

select weather.* 
  from weather
  inner join    
    (select city, max(date) from weather group by city) as latest
    on weather.date = latest.date
      and weather.city = latest.city

回复收藏 0 原文

孤云独去闲 2024-10-17 20:25:09

有几种方法。就我个人而言，我认为以下是最有表现力的：

 SELECT * FROM weather w1 WHERE NOT EXISTS
     (SELECT * FROM weather w2 WHERE w2.city = w1.city AND w2.date > w1.date)

There are several methods. Personally, I think the following is the most expressive:

 SELECT * FROM weather w1 WHERE NOT EXISTS
     (SELECT * FROM weather w2 WHERE w2.city = w1.city AND w2.date > w1.date)

回复收藏 0 原文

半城柳色半声笛 2024-10-17 20:25:09

选项 1：

select city, temparature, date 
from  weather t1
where date = (select max(date)
              from   weather t2
              where  t2.city=t1.city)

选项 2：

select t1.city, t1.temp, t1.date
from weather t1
where not exists (select 1
                  from weather t2
                  where t2.date > t1.date and t1.city=t2.city)

Option 1:

select city, temparature, date 
from  weather t1
where date = (select max(date)
              from   weather t2
              where  t2.city=t1.city)

Option 2:

select t1.city, t1.temp, t1.date
from weather t1
where not exists (select 1
                  from weather t2
                  where t2.date > t1.date and t1.city=t2.city)

回复收藏 0 原文

~没有更多了~