問題描述

我有一個表格，其中的結(jié)果使用ORDER"列進(jìn)行排序，例如:

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

我的問題是最初設(shè)置此訂單欄時盡可能高效且可重復(fù)使用.

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

我最初的想法是設(shè)置一個標(biāo)量函數(shù)，當(dāng)向表中添加新條目時，該函數(shù)可用作默認(rèn)值:

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)

當(dāng)用戶想要置換 2 個文檔時，我可以輕松切換 2 個訂單.

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

它工作得很好，但我現(xiàn)在有第二張桌子，我需要以同樣的方式設(shè)置，我很確定有更好的方法來做到這一點(diǎn).有什么想法嗎?

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?

推薦答案

聽起來您想要一個標(biāo)識列，一旦它獲得初始值，您就可以覆蓋該列.一種解決方案是有兩列，一次調(diào)用InitialOrder"，這是一個自動遞增標(biāo)識列，然后第二列稱為 doc_order，最初設(shè)置為與 InitialOrder 字段相同的值(甚至可能作為插入觸發(fā)器或存儲過程(如果您以這種方式進(jìn)行插入)，但讓用戶能夠編輯該列.

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.

它確實(shí)需要每條記錄額外的幾個字節(jié)，但可以解決您的問題，如果它有任何價值，您將同時擁有初始文檔順序和用戶重置順序.

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 對返回值進(jìn)行排序，以確保返回序列一致.

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.

這篇關(guān)于SQL Server 中的自定義排序的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！