在实际项目中,有时会碰到数据库SQL的特殊排序需求,举几个例子,作为参考。
1、自定义优先级
一种常见的排序需求是指定某个字段取值的优先级,根据指定的优先级展示排序结果。比如如下表:
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
如果想把某个字段优先级提高,用如下方法:
select name from fruit
order by case name
when 'Strawberry'