SQL：在每个组中获取第N个项目

I have a user table like this

我有这样的用户表

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

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

对于每个社区,我想得到第三个用户注册的时间。我可以使用MySql的“限制”SQL扩展轻松地为单个社区执行此操作。例如,对于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

But I can't figure out how to get the registration date of the 3rd user for all communities in a single SQL statement.

但我无法弄清楚如何在单个SQL语句中获取所有社区的第三个用户的注册日期。

Cheers, Don

2 个解决方案

#1

With an inner select:

内部选择:

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

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.

选择每个用户是其社区中第三个用户的用户集,由内部选择中的limit子句返回。

#2

Is this what you mean?

你是这个意思吗?

SELECT registration_date
FROM user
ORDER BY registration_date
LIMIT n

Where n is the user you are concerned about.

其中n是您关注的用户。

#1