SQL TSQL - 是否可以定义排序顺序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4789241/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
TSQL - Is it possible to define the sort order?
提问by Justin808
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 上运行。
回答by LittleBobbyTables - Au Revtheitroad
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.
并将您的桌子连接到这张新桌子上。
回答by OMG Ponies
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
回答by paxdiablo
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/strawberryorder (or even if not - see below), you may want to think about sacrificing some disk space to gain some speed.
如果这将是一个短期需求，请使用 case 语句。但是，如果您认为它可能存在一段时间，并且它总是orange/apple/strawberry有序的（或者即使不是 - 见下文），您可能需要考虑牺牲一些磁盘空间来获得一些速度。
Create a new column in your table called or_ap_stand 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 selectstatements.
由于该列中的数据发生更改的唯一时间是行更改时，因此这是最好的时间。成本将在少量写入而不是大量读取上产生，因此在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.
而且，如果您还需要其他排序顺序，那么这就是我将 column 称为的原因or_ap_st。您可以根据需要添加任意数量的其他排序列。
回答by turtlepick
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
回答by Kuitsi
Going further from turtlepick's answer:
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 中值的开头。
回答by The Danish
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) 主键）以保留插入顺序
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
