MySQL GROUP BY 某些字段的最新值

发布于 2024-11-29 06:11:51 字数 545 浏览 8 评论 0原文

我有一个这样的表：

ID    Type   UserID    Timestamp    
1     1      1000      1312389223
2     1      1000      1312389220
3     2      1000      1312389215
4     3      1000      1312389213
5     3      2000      1312389205
6     1      2000      1312389201
7     2      1000      1312389190

我现在需要的是仅检索该表中数据的某些部分。该列表显示与特定用户在我的网站上执行的操作相关的数据。某些“类型”需要显示所有条目。其他人只需要显示最新的。

在上面的示例中，我需要显示所有出现的 Type = 2，但仅显示 Type = 1 的前 1 个（按时间戳排序）。 Type = 3 也是如此。这是基于每个 UserID 的。

希望我已经解释得足够清楚了。

预先感谢您的帮助，费舍尔。

原文

I have a table like this:

ID    Type   UserID    Timestamp    
1     1      1000      1312389223
2     1      1000      1312389220
3     2      1000      1312389215
4     3      1000      1312389213
5     3      2000      1312389205
6     1      2000      1312389201
7     2      1000      1312389190

What I need now, is to retrieve only certain parts of the data in this table. The list shows data in relation to what a certain user has been doing on my site.
Some of the "Type"'s need to have all entries shown. Others need only to have the most recent shown.

In the above example I need to have all occurances of Type = 2 shown, but only top 1 (ordered by Timestamp) of Type = 1 shown. Same goes for Type = 3. This is per UserID basis.

Hope I have explained myself clear enough.

Thanks in advance for your help,
fischer.

分享到QQ

分享到微博

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

发布评论

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

等往事风中吹 2024-12-06 06:11:51

如果您不需要ID，则简单而快速的解决方案

select user, type, timestamp from Table where type = 2
union
select user, type, max(timestamp) from Table where type in (1,3) group by user, type

将保留ID，但它更复杂并且速度稍慢：

select * from Table where type = 2
union
select * 
from Table t
join (
    select type, user, max(timestamp) as timestamp
    from Table 
    where type in (1,3)
    group by type, user
) tt on tt.user = t.user and tt.type=t.type and tt.timestamp = t.timestamp

无论如何，这两种解决方案都不是很有可扩展性

Simple and quick solution if you dont need IDs

select user, type, timestamp from Table where type = 2
union
select user, type, max(timestamp) from Table where type in (1,3) group by user, type

That will keep IDs, but it's more complicated and somewhat slower:

select * from Table where type = 2
union
select * 
from Table t
join (
    select type, user, max(timestamp) as timestamp
    from Table 
    where type in (1,3)
    group by type, user
) tt on tt.user = t.user and tt.type=t.type and tt.timestamp = t.timestamp

Anyway, both solutions are not very scalable

回复收藏 0 原文

江湖彼岸 2024-12-06 06:11:51

select ID, 
       type, 
       userid, 
       max(timestamp)
from table
where type in (1, 3)
group by id, type, userid
union
select *
from table
where type = 2

select ID, 
       type, 
       userid, 
       max(timestamp)
from table
where type in (1, 3)
group by id, type, userid
union
select *
from table
where type = 2

回复收藏 0 原文

~没有更多了~