sql - 如何按自定义顺序对 T-SQL 查询输出进行排序？

Question

我正在使用SQL Server 2014. 我需要T-SQL按特定顺序对查询的输出进行排序。

我尝试了以下方法，但它没有按我的预期工作。这是我T-SQL查询的最后一部分的摘录：

...  
GROUP BY xx.[Market]

ORDER BY CASE
WHEN xx.[Market] = 'France' THEN '1'
WHEN xx.[Market] = 'United Kingdom' THEN '2'
WHEN xx.[Market] = 'Germany' THEN '3'
WHEN xx.[Market] = 'Belgium' THEN '4'
WHEN xx.[Market] = 'Reunion' THEN '5'
WHEN xx.[Market] = 'South Africa' THEN '6'
WHEN xx.[Market] = 'Russia' THEN '7'
WHEN xx.[Market] = 'Middle East' THEN '8'
WHEN xx.[Market] = 'Central Europe' THEN '9'
WHEN xx.[Market] = 'Poland' THEN '10'
WHEN xx.[Market] = 'Scandinavia' THEN '11'
WHEN xx.[Market] = 'Netherlands' THEN '12'
WHEN xx.[Market] = 'India' THEN '13'
WHEN xx.[Market] = 'G&I' THEN '14'
WHEN xx.[Market] = 'China' THEN '15'
WHEN xx.[Market] = 'OTA' THEN '16'
WHEN xx.[Market] = 'Web Direct' THEN '17'
WHEN xx.[Market] = 'DB Local' THEN '18'
WHEN xx.[Market] = 'DB International' THEN '19'
WHEN xx.[Market] = 'Other Markets' THEN '20'

ELSE xx.[Market] END

但是，我得到以下结果：

Market               RN
France               196
Scandinavia           80
Netherlands          320
India                  2
OTA                  372
Web Direct           140
DB Local              11
DB International      25
United Kingdom     2,424
Other Markets        116
Germany              609
Belgium              350
Reunion               27
South Africa          42
Russia                 7
Central Europe        17
Switzerland           34

是不是因为某些市场没有包含在输出中？如果是，我该如何处理这个问题（因为当我将查询与不同的过滤器一起使用时，可能会出现此输出中缺少的市场）？

标签： sqlsql-servertsqlsql-order-by

Answer 1

不要在 CASE 子句中使用字符串值，而是使用数值。

ORDER BY CASE
WHEN xx.[Market] = 'France' THEN 1
WHEN xx.[Market] = 'United Kingdom' THEN 2
...
ELSE 1000
END