如何在 SQL Server 中按字母數字程式碼排序

我有一張包含一些項目程式碼的表格。這些程式碼是

e.g.: B001, B002, Z15001, Z14001, P003,...

我需要這個結果表：

Z15001
Z15002
...
B001
B002
...
C001
C002
...
Z14099
Z14098
Z14097
...
Z13099
Z13098
...
Z13001

我試過這個，但順序是錯誤的。

select *
from table
order by
   case
       when Kod like 'Z15%' then 1
       when Kod like 'B%' then 2
       when Kod like 'C%' then 3
       when Kod like 'D%' then 4
       when Kod like 'E%' then 5 else 6 end asc, Kod asc
   , case when Kod NOT LIKE 'B%' 
       AND Kod NOT LIKE 'C%' 
       AND Kod NOT LIKE 'D%' 
       AND Kod NOT LIKE 'E%' 
       AND Kod NOT LIKE 'Z15%' then 6 
     end desc

我如何在 SQL Server 中做到這一點？

如果您對重複執行這種排序或在更大的數據集上執行感興趣，您可能需要考慮添加一個鍵表來控制排序順序，並加入該表。

首先，我們設置測試環境：

USE tempdb;
CREATE TABLE dbo.K
(   KOD VARCHAR(255) NOT NULL CONSTRAINT PK_K 
       PRIMARY KEY CLUSTERED
);

CREATE TABLE dbo.KSort
(
   KODSort INT NOT NULL CONSTRAINT PK_KSort
       PRIMARY KEY CLUSTERED
   , KODPrefix VARCHAR(255) NOT NULL 
);


INSERT INTO dbo.K (KOD)
VALUES ('Z15001')
   , ('Z15002')
   , ('B001'  )
   , ('B002'  )
   , ('C001'  )
   , ('C002'  )
   , ('Z14099')
   , ('Z14098')
   , ('Z14097')
   , ('Z13099')
   , ('Z13098')
   , ('Z13001');

INSERT INTO dbo.KSort(KODSort, KODPrefix)
VALUES (10, 'Z15%')
   , (20, 'B%')
   , (30, 'C%')
   , (40, 'Z14%')
   , (50, 'Z13%');

SELECT K.*
FROM dbo.K;

這顯示了預期的結果，它恰好按分群鍵的順序排序：

現在，有兩個查詢，第一個使用標準ORDER BY子句“手動”對結果進行排序。

SELECT *
FROM dbo.K
ORDER BY
   CASE
       WHEN Kod LIKE 'Z15%' THEN 1
       WHEN Kod LIKE 'B%' THEN 2
       WHEN Kod LIKE 'C%' THEN 3
       WHEN Kod LIKE 'Z14%' THEN 4
       WHEN Kod LIKE 'Z13%' THEN 5 
       ELSE 6 
   END ASC;

對此的計劃是：

很好很簡單。“排序”運營商承擔了計劃成本的 77.6%。

如果我們使用 JOIN 來完成排序，如下所示：

SELECT K.*
FROM dbo.K
   INNER JOIN dbo.KSort ON K.KOD LIKE KSort.KODPrefix
ORDER BY KSort.KODSort, K.KOD;

我們從一段更簡單的程式碼開始，這在深層上吸引了我；但是我們也看到了一個有趣的執行計劃：

我們現在有幾個嵌套循環連接，而不是排序運算符。

當處理少量數據時，一旦我們在 SQL Server 的緩衝區中有數據，差異就無關緊要了。

但是，讓我們看看當我們向KOD表中添加更多行時會發生什麼。

TRUNCATE TABLE dbo.K;

INSERT INTO dbo.K(KOD)
SELECT TOP(1000000) CASE ROW_NUMBER() OVER (ORDER BY o.object_id) % 5
  WHEN 0 THEN 'Z15' 
  WHEN 1 THEN 'B' 
  WHEN 2 THEN 'C' 
  WHEN 3 THEN 'Z14' 
  WHEN 4 THEN 'Z13' END
   + CONVERT(VARCHAR(255), ROW_NUMBER() OVER (ORDER BY o.object_id))
FROM sys.objects o, sys.objects o1, sys.columns c;

（上面添加了 1,000,000 行到表中）

在我的機器上，這是一個 4 核 Intel I7 和 16GB RAM，我看到以下SET STATISTICS IO,TIME ON;配置：

“標準”排序：

(1000000 row(s) affected)
Table 'K'. Scan count 9, logical reads 2679, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
  CPU time = 1094 ms,  elapsed time = 6463 ms.

“加入”：

(1000000 row(s) affected)
Table 'K'. Scan count 5, logical reads 2641, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'KSort'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
  CPU time = 437 ms,  elapsed time = 5475 ms.

我在開始每個查詢之前使用了 DBCC FREEPROCCACHE，多次執行的結果非常相似。通常，標準排序比 JOIN 版本慢 2 到 3 倍。

超越性能影響，使用我建議的鍵表 ( dbo.KSort) 允許您隨意更改輸出的排序順序，而無需修改程式碼，在我看來，這是一個非常好的獎勵。您只需修改KODSort列的值，以便以您喜歡的任何順序返回結果。例如，以下內容UPDATE將使Z14*行顯示在行之前B*：

UPDATE KSort SET KODSort = 15 WHERE KODSort = 40;

這將Z15,B,C,D,E首先按升序返回，然後按Kod降序返回其餘的：

ORDER BY
   CASE
       WHEN Kod LIKE 'Z15%' THEN 1
       WHEN Kod LIKE 'B%' THEN 2
       WHEN Kod LIKE 'C%' THEN 3
       WHEN Kod LIKE 'D%' THEN 4
       WHEN Kod LIKE 'E%' THEN 5 
       ELSE 6 
   END ASC, 
   CASE
       WHEN Kod LIKE 'Z15%' 
         OR Kod LIKE 'B%'
         OR Kod LIKE 'C%' 
         OR Kod LIKE 'D%' 
         OR Kod LIKE 'E%' 
       THEN Kod 
   END ASC, 
   Kod DESC

引用自：https://dba.stackexchange.com/questions/102422