获取结果集sql server中记录的行号

问题描述

我有一张包含序列号文档的表格 - 列:

I have a table full of documents with serial numbers - columns:

• docid int
• 书写文字
• 提交日期日期时间

...我想找出特定序列号在对表的查询中的位置.

...and I'd like to find out where a particular serial number lies in a query against the table.

例如，如果我按 submitDate asc 对结果集进行排序，我可能想知道文档 34 在该排序中的位置(即它是否在位置 11?)

For example, if I ordered the resultset by submissionDate asc, I might want to know where document 34 is within that ordering (i.e. is it in position 11?)

我该怎么做?

推荐答案

看起来您正在尝试获取行号，即使它不是要返回的行之一.您可以为此使用 CTE:

It looks like you're trying to get the row number even if that's not one of the rows being returned. You can use a CTE for that:

;WITH CTE AS
(
    SELECT
        docid,
        writing,
        submissionDate,
        ROW_NUMBER() OVER (ORDER BY submissionDate) AS position
    FROM
        My_Table
)
SELECT
    docid,
    writing,
    submissionDate,
    position
FROM
    CTE
WHERE
    docid = 34

这当然也需要 SQL Server 2005 或更高版本.

This also requires SQL Server 2005 or greater of course.