将查询设置为asc或desc命令？

Question

SELECT * FROM (SELECT ROW_NUMBER() 
over 
    (
     ORDER BY 
      CASE WHEN @SortExpression ='Country_id' THEN Country_id END, 
      CASE WHEN @SortExpression ='Country_name' THEN Country_name END, 
      CASE WHEN @SortExpression ='Country_region' THEN Country_region END, 
      CASE WHEN @SortExpression ='Country_area' THEN Country_area END, 
      CASE WHEN @SortExpression ='Country_Population' THEN Country_Population END, 
     CASE WHEN @SortExpression ='Country_gdp' THEN Country_gdp END 
    )as num ,* From Country_Profile123) as tbl 
     WHERE num BETWEEN @column AND @column1 

我解决了一半的问题（即分页和排序），现在我想要做的是排序顺序。

Answer 1

你在找这样的吗？

SELECT * FROM (SELECT ROW_NUMBER() 
over 
    (
     ORDER BY 
      CASE WHEN @SortExpression ='Country_id' THEN Country_id DESC END, 
      CASE WHEN @SortExpression ='Country_id_asc' THEN Country_id ASC END, 
      CASE WHEN @SortExpression ='Country_name' THEN Country_name DESC END, 
      CASE WHEN @SortExpression ='Country_name_asc' THEN Country_name ASC END, 
      CASE WHEN @SortExpression ='Country_region' THEN Country_region DESC END, 
      CASE WHEN @SortExpression ='Country_region_asc' THEN Country_region ASC END, 
      CASE WHEN @SortExpression ='Country_area' THEN Country_area DESC END, 
      CASE WHEN @SortExpression ='Country_area_asc' THEN Country_area ASC END, 
      CASE WHEN @SortExpression ='Country_Population' THEN Country_Population DESC END, 
      CASE WHEN @SortExpression ='Country_Population_asc' THEN Country_Population ASC END, 
     CASE WHEN @SortExpression ='Country_gdp' THEN Country_gdp DESC END 
     CASE WHEN @SortExpression ='Country_gdp_asc' THEN Country_gdp ASC END 
    )as num ,* From Country_Profile123) as tbl 
     WHERE num BETWEEN @column AND @column1 

Answer 2

使用单case语句，而不是重复相同的case语句为每一个可能值：

SELECT * FROM (
    SELECT ROW_NUMBER() 
    over (
    ORDER BY 
     CASE @SortExpression 
     WHEN 'Country_id' THEN Country_id 
     WHEN 'Country_name' THEN Country_name 
     WHEN 'Country_region' THEN Country_region 
     WHEN 'Country_area' THEN Country_area 
     WHEN 'Country_Population' THEN Country_Population 
     WHEN 'Country_gdp' THEN Country_gdp 
     END 
) as num, * From Country_Profile123 
) as tbl 
WHERE num BETWEEN @column AND @column1 

应用排序顺序是有点棘手。您无法将方向应用于该值，因此您需要具有不同方向的第一手表壳和第二手表壳：

SELECT * FROM (
    SELECT ROW_NUMBER() 
    over (
    ORDER BY 
     CASE @SortExpression 
     WHEN 'Country_id' THEN Country_id 
     WHEN 'Country_name' THEN Country_name 
     WHEN 'Country_region' THEN Country_region 
     WHEN 'Country_area' THEN Country_area 
     WHEN 'Country_Population' THEN Country_Population 
     WHEN 'Country_gdp' THEN Country_gdp 
     ELSE 0 
     END, 
     CASE @SortExpression 
     WHEN 'Country_id_desc' THEN Country_id 
     WHEN 'Country_name_desc' THEN Country_name 
     WHEN 'Country_region_desc' THEN Country_region 
     WHEN 'Country_area_desc' THEN Country_area 
     WHEN 'Country_Population_desc' THEN Country_Population 
     WHEN 'Country_gdp_desc' THEN Country_gdp 
     ELSE 0 
     END DESC 
) as num, * From Country_Profile123 
) as tbl 
WHERE num BETWEEN @column AND @column1