根据另一个字段按字母顺序设定排序顺序字段

Question

我最近在我的数据库（SQL Server 2005）中为一些表添加了几个字段，以允许用户自定义行的排序顺序。我已经遵循了所有表的这种模式：

-- Alter the InvoiceStatus table
ALTER TABLE [dbo].[InvoiceStatus] ADD [Disabled] bit NOT NULL DEFAULT 0
GO
ALTER TABLE [dbo].[InvoiceStatus] ADD [SortOrder] int NOT NULL DEFAULT 0
GO
-- Use the primary key as the default sort order
UPDATE [dbo].[InvoiceStatus]
   SET [SortOrder] = [InvoiceStatusId]
GO

通常情况下，我会将主键作为默认排序顺序。但现在，我想使用表格中文本字段的字母顺序作为默认排序顺序。以上表格为例（其中有一个文本字段[InvoiceStatusName]），是否有类似简洁明了的查询语句可以使用[InvoiceStatusName]的字母顺序作为默认排序顺序？

更新：问题已经得到解答，但是有些人指出这个解决方案可能并不理想，所以我想添加一些背景信息供将来参考。这是一个旧系统（不是传统的老系统，但已经存在了很多年），在多个不同的地方使用。

应用程序中有几个列表/下拉菜单，具有典型的“状态”类型（例如发票状态、订单状态、客户类型等）。当系统首次编写时，这些是每个地方都使用的标准值（不能以任何方式更改），但是一些用户开始要求添加新状态、删除不再使用的状态并指定自定义排序顺序（某个状态可能更频繁地使用，因此最好将其放在列表的顶部）。

我发现实现这一点最简单的方法（而不必大量修改旧代码）是向所有相关表添加两个新字段：Disabled和SortOrder。Disabled字段用于“隐藏”未使用的类型（由于引用完整性无法删除它们，并且它们所持有的值也需要保留），而SortOrder字段则允许用户指定自定义排序顺序。由于所有相关表格还共享这两个列，因此非常容易以通用方式处理排序（和禁用）的简单接口。

- Julian

Answer 1

;WITH so AS
(
SELECT 
   SortOrder,
   ROW_NUMBER() OVER (ORDER BY InvoiceStatusName) AS rn
FROM   dbo.InvoiceStatus
)
UPDATE so SET SortOrder = rn

- Martin Smith

---

+1 谢谢 Martin。今天我学到了新的东西。顺便说一下，您可以删除 InvoiceStatusID 列。 - Lieven Keersmaekers

感谢Martin和Lieven。你们两个的建议都很好，但我选择了Martin的作为被接受的建议，因为它看起来最整洁。 - Julian

Answer 2

你可以使用 ROW_NUMBER() 函数将排序后的名称映射到一个整数。

UPDATE  dbo.InvoiceStatus
SET     SortOrder = ivsn.Number
FROM    dbo.InvoiceStatus ivs
        INNER JOIN (
          SELECT dbo.InvoiceStatusID
                 , [number] = ROW_NUMBER() OVER (ORDER BY InvoiceStatusName)
          FROM   dbo.InvoiceStatus
        ) ivsn ON ivsn.InvoiceStatusID = ivs.InvoiceStatusID

你应该问自己，这个方案是否是解决你问题的最佳方案。目前的实现不具备良好的可扩展性。

- Lieven Keersmaekers

---

你不需要使用自连接来实现这个。 - Martin Smith

@Martin：我曾经思考过这个问题，但是无法想出如何在不使用自连接的情况下编写它。如果你能创建一个没有自连接的新答案，我会点赞它。 - Lieven Keersmaekers

@Lieven - 完成了！您可以更新表达式本身。（适用于CTE或派生表） - Martin Smith

@Martin - 现在我已经看到了CTE，如果使用派生表，它将如何工作？ - Lieven Keersmaekers

UPDATE so SET SortOrder = rn FROM (SELECT SortOrder, ROW_NUMBER() OVER (ORDER BY InvoiceStatusName) AS rn FROM InvoiceStatus) so - Martin Smith

没想到这么简单，谢谢你。也许你应该再写一个答案，这样我就可以再次点赞了。 - Lieven Keersmaekers

Answer 3

你选择实现表格排序的方式很不寻常，而且显然只能按整数进行排序。

我真的建议你用另一种方式重新设计你的排序子系统。

例如，在某个地方有一个元数据表，保存用户想要按其排序的列的名称，然后在相关查询中使用ORDER BY (columnname)。

编辑：哦等等，我们正在谈论查找表和让用户更改顺序。现在我理解了，你的实现对我来说更有意义。

- codeulike

---

不错的更新查询，Martin和Lieven。显然我是这个聚会上的不高兴的人。 - codeulike

对我来说，它听起来并不抱怨，就其价值而言，我也认为 OP 将想要更改他的设计以适应未来的请求。 - Lieven Keersmaekers

我的意图不是让用户选择表格应该按哪一列排序。他们应该能够控制行的实际排序顺序（用于应用程序中的展示）。 - Julian

好的，我可以看出在那种情况下[SotColumn]是有意义的。不过，将整个默认序列复制到其中似乎有点尴尬。如果文本列的内容发生变化会发生什么？ - codeulike

@codeulike：某些列被阻止编辑文本（因为它们具有特殊的上下文含义），但除此之外，如果用户更改文本列的内容，就不会发生任何特殊情况。我“复制”整个默认序列的唯一原因是，当我将用户数据库升级到下一个版本时，它应该具有一个初始的合理顺序（与今天的顺序相比（对于一些表格来说是按字母顺序排列的，但对于其他表格来说基本上与主键相同）。 - Julian

感谢您编辑问题，现在我对它的意思更加清楚了——我们正在谈论查找表和让人们选择项目顺序。想一想，我过去也使用过与您的[Disabled]和[SortOrder]列非常相似的系统：） - codeulike