如果提到，按特定值自定義 SQL 記錄集順序 (Customize SQL recordset order by specific value (s) if mentioned)

問題描述

如果提到，按特定值自定義 SQL 記錄集順序 (Customize SQL recordset order by specific value (s) if mentioned)

我有一個使用經典 ASP 和 SQL 編寫的在線商店 web 應用程序，我需要有關商品訂單結果的幫助。

我有一個名為“items”的表，如下所示：

ITEM_NUMBER | DESCRIPTION | PRICE | NEXT_ITEM |
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
0001          Item 0001       1.5
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
0002          Item 0002       2
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
0003          Item 0003       1
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
0004          Item 0004       3
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
0005          Item 0005       7        0030
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
.
.
.
.
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
0030          Item 0030       3

我想要做的是按ITEM_NUMBER保持訂單，因為它是按item no，因為NEXT_ITEM值為null，但是在item 0005之後顯示item 0030，因為它是非常相似的item，並且item number在項目 0005 的行。之後需要返回記錄集指針以繼續從它跳過的地方加載結果（項目 0006）。

一旦記錄集到達 0030，我希望它跳過這條記錄因為它已經對用戶可見並且之前已經加載過。

參考解法

方法 1:

This would seem to do the job ‑ you need to join the table back to itself, and use that self‑join in preference to the usual sort key:

declare @T table (ItemNo char(4) not null,Description varchar(20) not null,RelatedItemNo char(4) null)
insert into @T (ItemNo,Description,RelatedItemNo) values
('0001','0001',null),
('0002','0002',null),
('0003','0003',null),
('0004','0004',null),
('0005','0005','0009'),
('0006','0006',null),
('0007','0007',null),
('0008','0008',null),
('0009','0009',null),
('0010','0010',null)

select
    t.*
from
    @T t
        left join
    @T t_par
        on
            t.ItemNo = t_par.RelatedItemNo
order by
    COALESCE(t_par.ItemNo,t.ItemNo),
    t_par.RelatedItemNo

Result:

ItemNo Description          RelatedItemNo
‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑
0001   0001                 NULL
0002   0002                 NULL
0003   0003                 NULL
0004   0004                 NULL
0005   0005                 0009
0009   0009                 NULL
0006   0006                 NULL
0007   0007                 NULL
0008   0008                 NULL
0010   0010                 NULL

This doesn't deal well with the situation where an item appears as a RelatedItemNo for multiple other rows ‑ it would appear in each such location. But, there again, you didn't mention this possibility in your question, nor a specific rule for dealing with it, so I won't add any fix for this.

Also, if we need to extend this to multiple levels, the above is still inadequate ‑ fixable by introducing a CTE. But, again, you haven't mentioned such a situation in your question.

方法 2:

Depending on your version of SQL Server you could add a hierarchyid column to your table and sort on that.

Alternately you could write a recursive CTE to order the results.

(by Ofirk、Damien_The_Unbeliever、Anthony Faull)

參考文件

1. Customize SQL recordset order by specific value (s) if mentioned (CC BY‑SA 2.5/3.0/4.0)