SQL Server 2008使用RANK动态地编号行 [英] SQL Server 2008 Using RANK to Dynamically Number Rows

问题描述

我有一个存储过程，提取SS报告的数据。我需要包括一个行号列，但我从来没有使用RANK之前，我gettig这个错误：列'WL.SSN'在选择列表无效，因为它不包含在聚合函数或GROUP BY子句任何人都可以帮助我的语法？这是我到目前为止。

I have a stored procedure that pulls data for a SS Report. I need to include a Row Number column but I have never used RANK before and I'm gettig this error: Column 'WL.SSN' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.. Can anyone help me with the syntax? Here is what I have so far.

    SELECT
        WL.SSN,
        WL.LastName,
        WL.FirstName,
        WL.AddressLine1,
        WL.AddressLine2,
        WL.AddressLine3,
        WL.City,
        WL.State,
        WL.ZipCode,
        WL.Plus4,
        S.SeminarID,
        S.SeminarType,
        RANK = Count(*)                     
    FROM 
        @tblWaitList WL
        INNER JOIN @tblSeminar S ON WL.SeminarGuid=S.SeminarGuid
    WHERE
        S.SeminarID = @SeminarID AND S.SeminarType = @SeminarType
        AND RowNumber = COUNT(*)    
    ORDER BY
        RANK

我已经尝试过几乎每一种方法 http://support.microsoft.com/kb/186133 但我遗失了一些东西。

I have tried almost every approach to this from http://support.microsoft.com/kb/186133 but I'm missing something.

这里是我想要尝试的一个例子...

Here is an example of what I'm trying to acheive...

 Rank        Au_Lname              Au_Fname
 ----        --------------        -----------
 1         Bennet                Abraham
 2         Blotchet-Halls        Reginald
 3         Carson                Cheryl
 4         DeFrance              Michel
 5         del Castillo          Innes
 6         Dull                  Ann
 7         Greene                Morningstar
 8         Green                 Marjorie

推荐答案

这应该：

SELECT rank() OVER (ORDER BY WL.LastName, WL.FirstName) as rank, WL.LastName, WL.FirstName 
FROM  @tblWaitList WL         
INNER JOIN @tblSeminar S 
ON WL.SeminarGuid=S.SeminarGuid     
WHERE S.SeminarID = @SeminarID AND S.SeminarType = @SeminarType         
ORDER BY rank ;

这篇关于SQL Server 2008使用RANK动态地编号行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！