SQL Server 中的自定义排序 [英] Custom sort in SQL Server

问题描述

我有一个表格，其中的结果使用ORDER"列进行排序，例如:

I have a table where the results are sorted using an "ORDER" column, eg:

Doc_Id    Doc_Value   Doc_Order
1         aaa         1
12        xxx         5
2         bbb         12
3         ccc         24

我的问题是最初设置此订单栏时尽可能高效且可重复使用.

My issue is to initially set up this order column as efficiently and reusably as possible.

我最初的想法是设置一个标量函数，当向表中添加新条目时，该函数可用作默认值:

My initial take was to set up a scalar function that could be used as a default value when a new entry is added to the table:

ALTER FUNCTION [dbo].[Documents_Initial_Order] 
( )
RETURNS int
AS
BEGIN

RETURN (SELECT ISNULL(MAX(DOC_ORDER),0) + 1 FROM dbo.Documents)

当用户想要置换 2 个文档时，我可以轻松切换 2 个订单.

When a user wants to permute 2 documents, I can then easily switch the 2 orders.

它工作得很好，但我现在有第二张桌子，我需要以同样的方式设置，我很确定有更好的方法来做到这一点.有什么想法吗?

It works nicely, but I now have a second table I need to set up the same way, and I am quite sure there is a nicer way to do it. Any idea?

推荐答案

听起来您想要一个标识列，一旦它获得初始值，您就可以覆盖该列.一种解决方案是有两列，一次调用InitialOrder"，这是一个自动递增标识列，然后第二列称为 doc_order，最初设置为与 InitialOrder 字段相同的值(甚至可能作为插入触发器或存储过程(如果您以这种方式进行插入)，但让用户能够编辑该列.

It sounds like you want an identity column that you can then override once it gets it initial value. One solution would be to have two columns, once call "InitialOrder", that is an auto-increment identity column, and then a second column called doc_order that initially is set to the same value as the InitialOrder field (perhaps even as part of the insert trigger or a stored procedure if you are doing inserts that way), but give the user the ability to edit that column.

它确实需要每条记录额外的几个字节，但可以解决您的问题，如果它有任何价值，您将同时拥有初始文档顺序和用户重置顺序.

It does require an extra few bytes per record, but solves your problem, and if its of any value at all, you would have both the inital document order and the user-reset order available.

另外，我不确定您的 doc_order 是否需要唯一，但如果不需要，您可以按 doc_order 和 InitialOrder 对返回值进行排序，以确保返回序列一致.

Also, I am not sure if your doc_order needs to be unique or not, but if not, you can then sort return values by doc_order and InitialOrder to ensure a consistent return sequence.

这篇关于SQL Server 中的自定义排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！