sql-自定义排列顺序

SqlServer自定义排序

　　在实际项目中，有时会碰到数据库SQL的特殊排序需求，举几个例子，作为参考。

1、自定义优先级

　　一种常见的排序需求是指定某个字段取值的优先级，根据指定的优先级展示排序结果。比如如下表：

1 2 3 4 5 6 7

Create TABLE Fruit (id INT IDENTITY(1, 1) ,Name VARCHAR(50));   INSERT  INTO Fruit (Name) VALUES  ('Apple'); INSERT  INTO Fruit (Name) VALUES  ('Watermelon'); INSERT  INTO Fruit (Name) VALUES  ('Strawberry'); INSERT  INTO Fruit (Name) VALUES  ('Banana'); INSERT  INTO Fruit (Name) VALUES  ('Pear');

　　如果按照Name字段排序，结果是

Apple Banana Pear Strawberry Watermelon

　　如果想把某个字段优先级提高，用如下方法：

1 2 3 4 5 6 7

select name from fruit order by case name     when 'Strawberry' then 1     when 'Banana' then 2     when 'Apple' then 3     else 4 end

　　指定了Strawberry、Banana、Apple三条记录的排序优先级，则这三个按照指定的结果排序，其他的都指定为4，排在后面。

Strawberry Banana Apple Watermelon Pear

　　如果不指定else 4这句呢，结果如下：

1 2 3 4 5

Watermelon Pear Strawberry Banana Apple

　　因为不指定就是NULL，在排序中，NULL的优先级最高，排在前面。

2、多字段关联排序

　　考虑如下需求：有一个机构表，需要按照深度优先排序，也就是一个机构的下级机构和下下级机构的优先级比同级机构高。

　　表结构有三个字段：机构号、机构名称、上级机构号：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62

branchnum   branchname  supbranchnum 1       本部          00000 2       北京市分行　　　　   00001 3       天津市分行　　　　   00001 4       河北省分行　　　　   00001 5       山西省分行　　　　   00001 2001        北京中关村中心支行   00002 2002        北京王府井支行　　   00002 2006        北京奥运村支行　　   00002 2010        北京东城支行　　　   00002 2026        北京西城支行　　　   00002 2044        北京崇文支行　　　   00002 2061        北京宣武支行　　　   00002 2077        北京朝阳支行　　　   00002 2099        北京海淀支行　　　   00002 2135        北京丰台支行　　　   00002 2137        北京方庄中心支行　   00002 2154        北京首都机场支行　   00002 2160        北京通州支行　　　   00002 2169        北京大兴支行　　　   00002 2175        北京世纪财富中心支   00002 2178        北京顺义支行　　　   00002 2185        北京昌平支行　　　   00002 2194        北京平谷支行　　　   00002 2195        北京密云支行　　　   00002 2198        北京怀柔支行　　　   00002 2204        北京延庆支行　　　   00002 2206        北京金融中心支行　   00002 2209        北京中银大厦支行　   00002 2210        北京石景山支行　　   00002 2211        北京商务区支行　　   00002 2227        北京使馆区支行　　   00002 2228        北京国际贸易中心支   00002 2231        北京上地支行  00002 2232        北京投资广场支行　   00002 2233        北京雅宝路支行　　   00002 2354        天津大港支行　　　   00003 2361        天津和平支行　　　   00003 2382        天津河西支行　　　   00003 2398        天津南开支行　　　   00003 2412        天津红桥支行　　　   00003 2423        天津河北支行　　　   00003 2447        天津河东支行　　　   00003 2463        天津津南支行　　　   00003 2470        天津北辰支行　　　   00003 2478        天津东丽支行　　　   00003 2484        天津西青支行　　　   00003 2492        天津武清支行　　　   00003 2500        天津宝坻支行　　　   00003 2504        天津汉沽支行　　　   00003 2508        天津宁河支行　　　   00003 2510        天津蓟县支行　　　   00003 2519        天津静海支行　　　   00003 2523        天津津钢支行　　　   00003 2601        石家庄市机场路支行   00004 2626        石家庄市中山支行　   00004 2652        石家庄市裕东支行　   00004 2678        石家庄市裕华支行　   00004 3451        太原鼓楼支行　　　   00005 3479        太原平阳支行　　　   00005 3494        太原并州支行　　　   00005 3506        太原漪汾支行　　　   00005

　　可以写一个排序函数，对每一个机构计算它的排序值，排序值就等于上级机构号：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Create FUNCTION fn_compare (     @Branchnum int ) RETURNS int AS BEGIN     declare @returnVal int     select @returnVal=supBranchnum from Branch where Branchnum=@Branchnum     if(@returnVal=1)     Begin         set @returnVal=@Branchnum     End     return @returnVal END GO

　　然后通过如下语句查询排序结构：

1 2	select branchnum,branchname,supbranchnum from branch order by dbo.fn_compare(branchnum),branchnum

　　因为fn_compare函数中，对一个机构和它下级机构返回的排序值(ReturnVal)相等，所以为了使上级机构号排在下级机构前面，需要使用第二个排序字段Branchnum。排序结果如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

branchnum   branchname  supbranchnum 1       本部   　　　　　00000 2       北京市分行　　　　    00001 2001        北京中关村中心支行    00002 2002        北京王府井支行　　    00002 2006        北京奥运村支行　　    00002 2010        北京东城支行　　　    00002 2026        北京西城支行　　　    00002 2044        北京崇文支行　　　    00002 2061        北京宣武支行　　　    00002 2077        北京朝阳支行　　　    00002 2099        北京海淀支行　　　    00002 2135        北京丰台支行　　　    00002 2137        北京方庄中心支行　    00002 2154        北京首都机场支行　    00002 2160        北京通州支行　　　    00002 2169        北京大兴支行　　　    00002 2175        北京世纪财富中心支  00002 2178        北京顺义支行　　　    00002 2185        北京昌平支行　　　    00002 2194        北京平谷支行　　　    00002 2195        北京密云支行　　　    00002 2198        北京怀柔支行　　　    00002 2204        北京延庆支行　　　    00002 2206        北京金融中心支行　    00002 2209        北京中银大厦支行　    00002 2210        北京石景山支行　　    00002 2211        北京商务区支行　　    00002 2227        北京使馆区支行　　    00002 2228        北京国际贸易中心支  00002 2231        北京上地支行   00002 2232        北京投资广场支行　    00002 2233        北京雅宝路支行　　    00002 20170       北京房山支行　　　    00002 3       天津市分行　　　　    00001 2354        天津大港支行　　　    00003 2361        天津和平支行　　　    00003 2382        天津河西支行　　　    00003 2398        天津南开支行　　　    00003 2412        天津红桥支行　　　    00003 2423        天津河北支行　　　    00003 2447        天津河东支行　　　    00003 2463        天津津南支行　　　    00003 2470        天津北辰支行　　　    00003 2478        天津东丽支行　　　    00003 2484        天津西青支行　　　    00003 2492        天津武清支行　　　    00003 2500        天津宝坻支行　　　    00003 2504        天津汉沽支行　　　    00003 2508        天津宁河支行　　　    00003 2510        天津蓟县支行　　　    00003 2519        天津静海支行　　　    00003 2523        天津津钢支行　　　    00003 4       河北省分行　　　　    00001 2601        石家庄市机场路支行    00004 2626        石家庄市中山支行　    00004 2652        石家庄市裕东支行　    00004 2678        石家庄市裕华支行　    00004 5       山西省分行　　　　    00001 3451        太原鼓楼支行　　　    00005 3479        太原平阳支行　　　    00005 3494        太原并州支行　　　    00005 3506        太原漪汾支行　　　    00005