sql - 将空值排序在最后而不是最前面

Question

我正在尝试实现一个排序程序，它将根据下拉列表的值进行排序。发生的情况是，当对列表进行排序时，NULL 字段将被指定的替换值替换，在本例中为 ''。这是首先排序的，因为我们按升序排序，现在我想按 BUT 升序排序，最后使用空值。我怎样才能实现这个？

ORDER BY l.IsActive DESC, 
        CASE WHEN @SortOrder = 1 THEN n.DisplayName
             WHEN @SortOrder = 2 THEN CASE ec.IsEquipmentRelated
                                            WHEN  1 THEN ISNULL(el.ShopID,'') + 
                                                    ISNULL(ec.EquipmentAbbr,'') + 
                                                    ISNULL(el.ClassSequenceNumber,'') + 
                                                    ISNULL(el.EquipmentComponent,'') + 
                                                    ISNULL(el.CompSequenceNumber,'')
                                            WHEN  0 THEN ISNULL(ec.EquipmentAbbr,'')
                                            ELSE NULL
                                      END
             ELSE l.DisplayName
        END

编辑:运行 MS SQL Server 2008/T-SQL

编辑: @Joe Stefanelli，我已经尝试过这个，它没有编译:

ORDER BY l.IsActive DESC, 
    CASE WHEN @SortOrder = 0 THEN l.DisplayName
         WHEN @SortOrder = 1 THEN CASE WHEN n.DisplayName = '' THEN 2 ELSE 1 END, n.DisplayName
         WHEN @SortOrder = 2 THEN 
                                  CASE ec.IsEquipmentRelated
                                        WHEN  1 THEN ISNULL(el.ShopID,'') + 
                                            ISNULL(ec.EquipmentAbbr,'') + 
                                            ISNULL(el.ClassSequenceNumber,'') + 
                                            ISNULL(el.EquipmentComponent,'') + 
                                            ISNULL(el.CompSequenceNumber,'')
                                        WHEN  0 THEN ISNULL(ec.EquipmentAbbr,'')
                                        ELSE NULL
                                  END
     END

Answer 1

...
ORDER BY CASE WHEN YourField = '' THEN 2 ELSE 1 END, YourField