SQL:在每个组中获取第N个项目 [英] SQL: get Nth item in each group
问题描述
我有一个这样的用户表
user_id | community_id | registration_date
--------------------------------------------
1 | 1 | 2008-01-01
2 | 1 | 2008-05-01
3 | 2 | 2008-01-28
4 | 2 | 2008-07-22
5 | 3 | 2008-01-11
对于每个社区,我想获取第三位用户注册的时间.我可以使用MySql的'limit'SQL扩展轻松地对单个社区执行此操作.例如,对于ID = 2的社区
For each community, I would like to get the time that the 3rd user registered. I can easily do this for a single community using MySql's 'limit' SQL extension. For example, for community with ID=2
select registration_date
from user
order by registration_date
where community_id = 2
limit 2, 1
或者,我可以通过以下方式获取第一个用户向所有社区注册的日期:
Alternatively, I can get the date that the first user registered for all communities via:
select community_id, min(registration_date)
from user
group by 1
但是我不知道如何在单个SQL语句中获取 all 社区的第三位用户的注册日期.
But I can't figure out how to get the registration date of the 3rd user for all communities in a single SQL statement.
干杯, 唐
推荐答案
具有内部选择:
select
registration_date, community_id
from
user outer
where
user_id IN (
select
user_id
from
user inner
where
inner.community_id = outer.community_id
order by
registration_date
limit 2,1
)
order by registration_date
选择一组用户,其中每个用户都是内部选择中的limit子句返回的社区中的第3个用户.
Selects the set of users where each user is the 3rd user in their community as returned by the limit clause in the inner select.
这篇关于SQL:在每个组中获取第N个项目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!