的SQL Server ROW_NUMBER行为

问题描述：

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开始围绕行数。我希望能够重置Row_Number与每个不同的UniversityID。

我试过如下：

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

我得到

消息207，级别16，状态1，行4无效的列名'RowNum'。

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

答

你只需要将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之前。在SELECT之前处理了WHERE。在row_number

谢谢，我已经有了内内选择一个WHERE子句，但我没有注意到，我也有过这是导致选择带来UniversityID = 1 or子句和2（由于OR）。 – 2013-03-26 19:59:38

答

使用分区：

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

的SQL Server ROW_NUMBER行为

相关推荐