問題描述
我有一個表格，其中的結果使用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 中的自定義排序的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！