SQL Server中的自定义排序

Question

我必须对数据进行排序，并且我希望根据Ship to the country列对数据进行排序。

Waybill #       Fob Code Order Type               Ship To Country
-----------------------------------------------------------------
PEN00070420     FCA POE  SGS-541                  DE    
SPM-SCRAP       FCA POE  SGS-541                  SG    
DIM027-055239   FCA POE  SGS-541                  SG    
DIM027-055085   FCA POE  SGS-541                  HK    
DIM027-054845   FCA POE  SGS-541                  HK    
F722954503      FCA POE  USO-821                  US    
ATKDGFHKG0163   FCA POE  SGS-541                  US    
PEN452450420    FCA POE  SGS-541                  DE

我需要这个数据集作为我的结果：

Waybill #       Fob Code Order Type               Ship To Country
-----------------------------------------------------------------
PEN00070420     FCA POE  SGS-541                  DE    
DIM027-055085   FCA POE  SGS-541                  HK    
SPM-SCRAP       FCA POE  SGS-541                  SG    
F722954503      FCA POE  USO-821                  US    
PEN452450420    FCA POE  SGS-541                  DE    
DIM027-054845   FCA POE  SGS-541                  HK    
DIM027-055239   FCA POE  SGS-541                  SG    
ATKDGFHKG0163   FCA POE  SGS-541                  US

在这里，DE，SG，HK，US都在重复，而且一直在重复。如何在SQL Server中执行这种排序？

Answer 1

您可以使用row_number()

order by row_number() over (partition by shipto order by waybill),
         shipto

Answer 2

你需要点两次。

通过按ShipToCountry分区和按shipTocountry排序来排序一次。现在，您在步骤1的结果中获得了ShipTocountry partitions

• 的排名，您必须再次根据shipToCountry对它们进行排序，以便按顺序列出排名的国家。

DECLARE @table table(waybill VARCHAR(30),Febcode VARCHAR(30),OrderType VARCHAR(30), ShipTocountry VARCHAR(30))

INSERT INTO @table values

('PEN00070420  ','FCA POE','SGS-541','DE'),    
('SPM-SCRAP    ','FCA POE','SGS-541','SG'),    
('DIM027-055239','FCA POE','SGS-541','SG'),    
('DIM027-055085','FCA POE','SGS-541','HK'),    
('DIM027-054845','FCA POE','SGS-541','HK'),    
('F722954503   ','FCA POE','USO-821','US'),    
('ATKDGFHKG0163','FCA POE','SGS-541','US'),    
('PEN452450420 ','FCA POE','SGS-541','DE');

SELECT Waybill, Febcode, OrderType, ShipTocountry
FROM
(
SELECT Waybill, Febcode, OrderType, ShipTocountry,
ROW_NUMBER() OVER  (PARTITION BY ShipToCountry ORDER BY shipToCountry) as rnk
from @table
) as t
order by rnk,ShipTocountry

+---------------+---------+-----------+---------------+
|    Waybill    | Febcode | OrderType | ShipTocountry |
+---------------+---------+-----------+---------------+
| PEN00070420   | FCA POE | SGS-541   | DE            |
| DIM027-055085 | FCA POE | SGS-541   | HK            |
| SPM-SCRAP     | FCA POE | SGS-541   | SG            |
| F722954503    | FCA POE | USO-821   | US            |
| PEN452450420  | FCA POE | SGS-541   | DE            |
| DIM027-054845 | FCA POE | SGS-541   | HK            |
| DIM027-055239 | FCA POE | SGS-541   | SG            |
| ATKDGFHKG0163 | FCA POE | SGS-541   | US            |
+---------------+---------+-----------+---------------+