MySQL 获取具有不同值的数据集

发布于 2024-10-25 03:11:11 字数 776 浏览 1 评论 0原文

我有一个简单的问题；我认为会有一个简单的解决方案。想象一个这样的表：

Name           Timestamp              Data
Bob            2011-01-01 01:00:00    Hi
Alice          2011-02-02 02:00:00    Hello
Alice          2011-03-03 03:00:00    Hello
Bob            2011-04-04 04:00:00    Bye
Charlie        2011-05-05 05:00:00    Cheese
Charlie        2011-06-06 06:00:00    Toast

我想要的只是能够运行一个查询来显示每个名称的最新条目。因此，对于上表，我想要这样的输出：

Name           Timestamp              Data
Bob            2011-04-04 04:00:00    Bye
Alice          2011-03-03 03:00:00    Hello
Charlie        2011-06-06 06:00:00    Toast

按数据排序。我不知道该怎么做。我以为我可以这样做：

SELECT DISTINCT(Name), timestamp, Data FROM Table ORDER BY Data

但这不起作用。任何帮助将不胜感激，干杯。

原文

I have a simple problem; one which I thought would have a simple solution. Imagine a Table like this:

Name           Timestamp              Data
Bob            2011-01-01 01:00:00    Hi
Alice          2011-02-02 02:00:00    Hello
Alice          2011-03-03 03:00:00    Hello
Bob            2011-04-04 04:00:00    Bye
Charlie        2011-05-05 05:00:00    Cheese
Charlie        2011-06-06 06:00:00    Toast

All I want is to be able to run a query that shows the most recent entry for each Name. So with the above table, I would like an output like this:

Name           Timestamp              Data
Bob            2011-04-04 04:00:00    Bye
Alice          2011-03-03 03:00:00    Hello
Charlie        2011-06-06 06:00:00    Toast

Ordered by Data. I can't figure out how to do this. I though I could just do:

SELECT DISTINCT(Name), timestamp, Data FROM Table ORDER BY Data

But this doesn't work. Any help would be most appreciated,
Cheers.

分享到QQ

分享到微博

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

发布评论

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

笑着哭最痛 2024-11-01 03:11:11

使用此查询获取最后的唯一值，

SELECT * FROM (select * from `table` order by date DESC) t group by name

Use this query to get last unique values,

SELECT * FROM (select * from `table` order by date DESC) t group by name

回复收藏 0 原文

慢慢从新开始 2024-11-01 03:11:11

SELECT DISTINCT Name, Timestamp, Data
FROM Table
WHERE Timestamp =
    (
     SELECT MAX(Timestamp)
     FROM Table AS tempTable
     WHERE Table.Name = tempTable.Name
    )

SELECT DISTINCT Name, Timestamp, Data
FROM Table
WHERE Timestamp =
    (
     SELECT MAX(Timestamp)
     FROM Table AS tempTable
     WHERE Table.Name = tempTable.Name
    )

回复收藏 0 原文

梦屿孤独相伴 2024-11-01 03:11:11

select t1.* from table as t1
inner join (
select name,max(`timestamp`) as `timestamp`
from table
group by name) as t2
on t1.name = t2.name and t1.`timestamp` = t2.`timestamp`

select t1.* from table as t1
inner join (
select name,max(`timestamp`) as `timestamp`
from table
group by name) as t2
on t1.name = t2.name and t1.`timestamp` = t2.`timestamp`

回复收藏 0 原文

~没有更多了~