SQL Server:WHERE子句中的行数()返回“无效列名”

countdown_order 是列别名。不能引用同一级别的列别名。

但是,您可以在更高的级别上执行此操作,如派生表或cte

SELECT *
FROM
(
    SELECT 
          ROW_NUMBER() OVER (ORDER BY country_code desc, 
                                      city_name desc) as countdown_order,
          AVG(latitude) AS latitude, 
          AVG(longitude) AS longitude, 
          city_name, 
          country_code   
    FROM  ip2location_db11 
    GROUP BY country_code, city_name
) as D
where countdown_order < 100   -- countdown_order is an integer, remove the single quote
ORDER BY country_code, city_name 

您也可以使用公共表表达式(cte)来实现。看起来像这样:

;WITH MAIN_CTE AS (
SELECT 
          ROW_NUMBER() OVER (ORDER BY country_code desc, 
                                      city_name desc) as countdown_order,
          AVG(latitude) AS latitude, 
          AVG(longitude) AS longitude, 
          city_name, 
          country_code   
    FROM  ip2location_db11 
    GROUP BY country_code, city_name
) 
SELECT * FROM MAIN_CTE 
WHERE countdown_order < 100
ORDER BY country_code, city_name