TSQL - 是否可以定义排序顺序？

Is it possible to define a sort order for the returned results?

是否可以为返回的结果定义排序顺序？

I would like the sort order to be 'orange' 'apple' 'strawberry' not ascending or descending.

我希望排序顺序是'橙色''苹果''草莓'不上升或下降。

I know ORDER BY can do ASC or DESC but is there a DEFINED('orange', 'apple', 'strawberry') type thing?

我知道ORDER BY可以做ASC或DESC但是有一个DEFINED（'orange'，'apple'，'strawberry'）类型的东西？

This will be running on SQL Server 2000.

这将在SQL Server 2000上运行。

7 个解决方案

#1

It's incredibly clunky, but you can use a CASE statement for ordering:

它非常笨重，但您可以使用CASE语句进行排序：

SELECT * FROM Blah 
ORDER BY CASE MyColumn 
    WHEN 'orange' THEN 1 
    WHEN 'apple' THEN 2 
    WHEN 'strawberry' THEN 3 
    END

Alternately, you can create a secondary table which contains the sort field and a sort order.

或者，您可以创建包含排序字段和排序顺序的辅助表。

TargetValue  SortOrder
orange       1
apple        2
strawberry   3

And join your table onto this new table.

并将您的表格加入这个新表格。

#2

Use a CASE statement:

使用CASE声明：

ORDER BY CASE your_col
           WHEN 'orange' THEN 1
           WHEN 'apple' THEN 2
           WHEN 'strawberry' THEN 3
         END

Alternate syntax, with an ELSE:

使用ELSE的备用语法：

ORDER BY CASE 
           WHEN your_col = 'orange' THEN 1
           WHEN your_col = 'apple' THEN 2
           WHEN your_col = 'strawberry' THEN 3
           ELSE 4
         END

#3

If this is going to be a short-lived requirement, use a case statement. However, if you think it may be around for a while, and it's always going to be orange/apple/strawberry order (or even if not - see below), you may want to think about sacrificing some disk space to gain some speed.

如果这将是一个短期要求，请使用案例陈述。但是，如果你认为它可能存在一段时间，并且它总是会是橙色/苹果/草莓顺序（或者即使不是 - 见下文），你可能想要牺牲一些磁盘空间来获得一些速度。

Create a new column in your table called or_ap_st and use an insert/update trigger to populate it with the number 1, 2 or 3, depending on the the value of your fruit column. Then index on it.

在表中创建一个名为or_ap_st的新列，并使用插入/更新触发器将其填入数字1,2或3，具体取决于水果列的值。然后索引它。

Since the only time the data in that column will change is when the row changes, that's the best time to do it. The cost will then be incurred on a small number of writes rather than a large number of reads, hence amortised over the select statements.

由于该列中的数据唯一更改的时间是行更改时，这是执行该操作的最佳时间。然后，成本将在少量写入而不是大量读取上产生，因此在select语句上摊销。

Your query will then be a blindingly fast:

您的查询将是一个非常快的查询：

select field1, field2 from table1
order by or_ap_st;

with no per-row functions killing the performance.

没有每行功能杀死性能。

And, if you want other sort orders as well, well, that's why I called the column or_ap_st. You can add as many other sorting columns as you need.

而且，如果您还想要其他排序顺序，那就是我调用列or_ap_st的原因。您可以根据需要添加任意数量的其他排序列。

#4

What I do in that case is

在这种情况下我做的是

ORDER BY
  CASE WHEN FRUIT = 'Orange' THEN 'A' 
       WHEN FRUIT = 'Apple' THEN 'B'
       WHEN FRUIT = 'Strawberry' THEN 'C'
       ELSE FRUIT
END

#5

Going further from turtlepick's answer:

从turtlepick的回答更进一步：

ORDER BY
  CASE WHEN FRUIT = 'Orange' THEN 'A' 
       WHEN FRUIT = 'Apple' THEN 'B'
       WHEN FRUIT = 'Strawberry' THEN 'C'
       ELSE FRUIT
  END

In case you have some more items in FRUIT and they happen to start with letters defined after THEN keywords, those items would appear within the hardcoded order. For example Banana shows up before Strawberry. You can circumvent it with

如果您在FRUIT中有更多项目，并且它们恰好以THEN关键字后定义的字母开头，那么这些项目将出现在硬编码顺序中。例如香蕉出现在草莓之前。你可以用它来规避它

ORDER BY
  CASE
    WHEN FRUIT = 'Orange' THEN '.1'
    WHEN FRUIT = 'Apple' THEN '.2'
    WHEN FRUIT = 'Strawberry' THEN '.3'
    ELSE FRUIT
  END

Here I have used characters with lower ASCII values in hope that they would not appear at the beginning of values in FRUIT.

在这里，我使用了具有较低ASCII值的字符，希望它们不会出现在FRUIT中的值的开头。

#6

Add a key to the table (e.g. fruit_id int identity(1,1) primary key) to preserve the order of insert

向表中添加一个键（例如fruit_id int identity（1,1）主键）以保留insert的顺序

create table fruit(fruit_id int identity(1,1) primary key, name varchar(50))
go

insert into fruit(name) values ('orange')
insert into fruit(name) values ('apple')
insert into fruit(name) values ('strawberry')

select name from fruit

result:

结果：

orange
apple
strawberry

#7

Not as common, but for single value operations or specific patterns, REPLACE works too

不常见，但对于单值操作或特定模式，REPLACE也可以

eg.

例如。

DECLARE @Fruit TABLE (Fruit_Id INT IDENTITY(1, 1) PRIMARY KEY ,Name VARCHAR(50));

INSERT  INTO @Fruit (Name) VALUES  ('Orange');
INSERT  INTO @Fruit (Name) VALUES  ('Apple');
INSERT  INTO @Fruit (Name) VALUES  ('Strawberry');
INSERT  INTO @Fruit (Name) VALUES  ('__Pear');


SELECT * FROM @Fruit AS f
ORDER BY REPLACE(f.Name,'__','')



Fruit_Id    Name
----------- --------------------------------------------------
2           Apple
1           Orange
4           __Pear
3           Strawberry

#1