SQL Server ROW_NUMBER behavior

Question

我有以下查询:

SELECT * FROM
(
    SELECT 
       a.TeacherID, a.UniversityID, 
       ROW_NUMBER() OVER (ORDER BY a.TeacherID) AS RowNum 
    FROM 
       SelectAll a  
    LEFT JOIN 
       mp_Ratings r ON a.TeacherID = r.TeacherID 
    GROUP BY 
       a.TeacherID, a.UniversityID
) AS TeacherInfo 
WHERE RowNum BETWEEN 10 AND 50

，它工作得很好。现在，如果我想这样做:

SELECT * FROM
(
    SELECT 
       a.TeacherID, a.UniversityID, 
       ROW_NUMBER() OVER (ORDER BY a.TeacherID) AS RowNum 
    FROM 
       SelectAll a  
    LEFT JOIN 
       mp_Ratings r ON a.TeacherID = r.TeacherID 
    GROUP BY 
       a.TeacherID, a.UniversityID
) AS TeacherInfo 
WHERE RowNum BETWEEN 10 AND 50 AND UniversityID = 2

我没有得到任何东西，因为UniversityID = 2从行号2991开始。我希望能够用每个不同的UniversityID重置Row_Number。

我试了如下:

SELECT *, ROW_NUMBER() OVER (ORDER BY TeacherID) AS RowNum  
FROM
(
    SELECT 
        a.TeacherID, a.UniversityID 
    FROM 
        SelectAll a  
    LEFT JOIN 
        mp_Ratings r ON a.TeacherID = r.TeacherID 
    GROUP BY 
        a.TeacherID, a.UniversityID
) AS TeacherInfo 
WHERE UniversityID = 2

为每次搜索带来新的row_numbers集，但如果我尝试使用RowNum别名如下:

SELECT *, ROW_NUMBER() OVER (ORDER BY TeacherID) AS RowNum  
FROM
(
    SELECT 
        a.TeacherID, a.UniversityID 
    FROM 
        SelectAll a  
    LEFT JOIN 
        mp_Ratings r ON a.TeacherID = r.TeacherID 
    GROUP BY 
        a.TeacherID, a.UniversityID
) AS TeacherInfo 
WHERE UniversityID = 2 
  AND RowNum BETWEEN 10 AND 20

我得到

Msg 207, Level 16, State 1, Line 4 .无效列名'RowNum'.

我的选择有什么问题?为什么我不能访问RowNum别名?

Answer 1

您只需要将WHERE子句移动到内部查询。

SELECT TeacherID, UniversityID, RowNum FROM
(
    SELECT a.TeacherID, a.UniversityID, ROW_NUMBER() OVER 
     (ORDER BY a.TeacherID) AS RowNum FROM SelectAll a
     LEFT JOIN mp_Ratings r 
     ON a.TeacherID = r.TeacherID 
     WHERE UniversityID = 2
     GROUP BY a.TeacherID, a.UniversityID
) as TeacherInfo WHERE RowNum BETWEEN 10 AND 50;

你不能在查询的外部版本访问RowNum别名，因为别名还不存在。SELECT在ORDER BY之前，倒数第二。WHERE在SELECT之前处理

Answer 2

使用row_number分区:

row_number() over(partition by UniversityID, order by teacherid)