获取resultset sql server中的记录行号

看起来您正在尝试获取行号,即使这不是要返回的行之一。你可以用CTE来做:

;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或更高版本。

SELECT t.docid,
       t.writing,
       t.submissiondate,
       ROW_NUMBER() OVER(ORDER BY t.submissiondate) AS position
  FROM YOUR_TABLE t

非分析版本

这种方法存在非连续值的风险,因为如果有两个(或更多)记录具有相同的 submissiondate 值——它们都有相同的位置值。这与使用秩或稠密秩分析函数是等价的,因为行数总是为每个记录赋予唯一值,而不考虑重复值。

SELECT x.docid,
       x.writing,
       x.submissiondate,
       (SELECT COUNT(*)
          FROM YOUR_TABLE y
         WHERE y.submissiondate <= x.submissiondate) AS position
  FROM YOUR_TABLE x

rank() .

from MSDN ):

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,RANK() OVER (ORDER BY i.Quantity DESC) AS 'RANK'
  FROM Production.ProductInventory i 
 INNER JOIN Production.Product p  ON i.ProductID = p.ProductID
 ORDER BY p.Name;

它是SQL标准,应该在SQL Server 2005和更高版本中工作。