在SQL数据库中获取第n条记录，其中表是另一个查询的结果。

I have number of users, each taking 10 questions and leave some time stamps. What I ideally want to do is to find the complete time of the n-th user where n is determined by the ascending order of time stamps, not the user ids.

我有很多用户，每个人回答10个问题并留下一些时间戳。我理想的做法是找到第n个用户的完整时间，其中n是由时间戳的升序决定的，而不是用户id。

This will not work, but to give you a feel of what I need I would be very happy if this could run:

这是行不通的，但为了让你们了解我需要什么，我很高兴如果这能实现:

SELECT <N-TH> MAX(u.time) AS maxTime FROM Users u
Group BY u.userId   ORDER BY maxTime

Any thoughts? right now I am running

任何想法吗?现在我正在跑步

SELECT MAX(u.time) AS maxTime FROM Users u
Group BY u.userId   ORDER BY maxTime

get the result as array and then find the n-th entry.

获取结果作为数组，然后找到第n项。

4 个解决方案

#1

GROUP BY UserId, and apply ROW_NUMBER based on max(u.time) to create a ranking pseudo-column that you can then filter on

根据UserId分组，并基于max(utime)应用ROW_NUMBER创建一个级别伪列，然后可以对其进行筛选

select min_utime, max_utime, userId from (
select min(u.time) min_utime, max(u.time) max_utime, u.userId,
row_number() over (order by max(u.time) asc) as ranker
from Users u
group by u.userId
) Z  where ranker  = @n

#2

Have you tried using ROW_NUMBER() function

您尝试过使用ROW_NUMBER()函数吗

SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY maxTime) as RowNum, MAX(u.time) AS maxTime FROM Users u
Group BY u.userId
) A
WHERE RowNum = NthNumber
ORDER BY maxTime

where NthNumber is the ranking number.

其中NthNumber是排序号。

#3

Get the first 10 rows as sorted by the maximum time:

获取按最大时间排序的前10行:

SELECT TOP 10 userId, MAX(time) AS maxtime
FROM Users
GROUP BY userId
ORDER BY maxtime ASC

Then just reverse the order and take the top row:

然后倒转顺序，取第一行:

SELECT TOP 1 *
FROM (
  SELECT TOP 10 userId, MAX(time) AS maxtime
  FROM Users
  GROUP BY userId
  ORDER BY maxtime ASC
) s
ORDER BY maxtime DESC;

#4

Many possible ways but just off cuff...could use CTEs or subquery. Try

有很多可能的方法，但只是即兴发挥……可以使用cte或子查询。试一试

SELECT MAX([maxTime]) AS [LastEntry] FROM (
    SELECT MAX(u.time) AS maxTime,u.userId 
    FROM Users u 
    GROUP BY u.userId,u.time) t

OK, try this...

好吧,试试这个…

WITH CTE ([maxTime], [userId ]) AS (
    SELECT MAX([time]) AS [maxTime],
        [userId]
    FROM Users
    GROUP BY [userId])
    SELECT MAX([maxTime]) AS [maxTime]
    FROM CTE;

HTH

Dave

戴夫

#1