SQL使用大小写排序相等的值

A similar question is already asked here. But current scenario is little bit complex than previous. In the example if same Itime then we can can sort by case but if Itime and result is same then how can I sort.

这里已经提出了类似的问题。但目前的情况比以前有点复杂。在示例中，如果Itime相同，那么我们可以按案例排序，但如果Itime和结果相同，那么我该如何排序。

My question is, here the result ID: 3,5,6,1,2,7,8,4. Why it is 2,7,8 for fail case .

我的问题是，结果ID：3,5,6,1,2,7,8,4。为什么失败案例是2,7,8。

Why it is not 8,2,7?

为什么不是8,2,7？

If I want the expected result like: 3,5,1,6,8,2,7,4 how can I proceed?

如果我想要预期的结果如：3,5,1,6,8,2,7,4我该怎么办？

Please run the below commands and help me to sort. Thanks in advance.

请运行以下命令并帮我排序。提前致谢。

if object_id('tempdb.dbo.#temp321','U') is not null
   drop table tempdb.dbo.#temp321

create table #temp321(id int, uname varchar(50), current_point int, 
                      previous_point int, ITime datetime, Result varchar(10))

INSERT into #temp321 values('1','a','50','40','2012-11-12 13:12:28.103','pass')
INSERT into #temp321 values('2','b','15','10','2012-11-12 13:12:28.103','fail')
INSERT into #temp321 values('3','c','71','70','2012-11-12 12:58:30.000','pass')
INSERT into #temp321 values('4','d','34','30','2012-11-12 13:12:28.103','withdraw')
INSERT into #temp321 values('5','e','40','35','2012-11-12 12:58:41.360','withdraw')
INSERT into #temp321 values('6','f','65','60','2012-11-12 13:12:28.103','pass')
INSERT into #temp321 values('7','g','20','15','2012-11-12 13:12:28.103','fail')
INSERT into #temp321 values('8','h','10','7','2012-11-12 13:12:28.103','fail')


select 
    ID 
from 
    #temp321 
ORDER BY 
    ITime ASC,
    CASE Result 
       WHEN 'pass' THEN 1 
       WHEN 'fail' THEN 2 
       WHEN 'withdrow' THEN 3 
    END 

drop table #temp321

Current output ID: 3,5,6,1,2,7,8,4
当前输出ID：3,5,6,1,2,7,8,4
Expected Output ID: 3,5,1,6,8,2,7,4
预期产出ID：3,5,1,6,8,2,7,4

2 个解决方案

#1

The current query will NOT deliver the same order every time.

当前查询不会每次都提供相同的订单。

For me your example delivers:

对我来说，你的例子是：

3, 5, 1, 6, 2, 7, 8, 4 (Note 1 and 6 being swapped)

1 and 6 are "equal" compared to their sort values taking into account for sorting. And if no sorting is specified (or equal sortings) the order within that bunch is - per definition - undefined. (depends on the order threads created the data)

考虑到排序，与排序值相比，图1和图6中的“排序”是“相等的”。如果没有指定排序（或相同的排序），则该组中的顺序是 - 按定义 - 未定义。（取决于创建数据的顺序线程）

Same applies for 2, 7, 8. You want the order 3, 5, 1, 6, 8, 2, 7, 4 - so you seem to "have" a logic how you expect it to be sorted? Then add that condition and you are done :)

同样适用于2,7,8。你想要订单3,5,1,6,8,2,7,4 - 所以你似乎“拥有”一个逻辑，你期望它如何排序？然后添加该条件，你就完成了:)

(for your expected output adding current_point is what you want - but YOU have to know if you want to sort by that column)

（对于您的预期输出，添加current_point是您想要的 - 但您必须知道是否要按该列排序）

SELECT * 
FROM temp321 
ORDER BY ITime ASC,
  CASE Result 
    WHEN 'pass'     THEN 1 
    WHEN 'fail'     THEN 2 
    WHEN 'withdraw' THEN 3 
  END, current_point ASC

#2

You can create subquery with as key value pair:

您可以使用键值对创建子查询：

   SELECT 'pass' value, 1 priority UNION ALL         
   SELECT 'fall' value, 2 priority UNION ALL
   SELECT 'withdraw' value, 3 priority UNION ALL

Then do a join to a subquery on value column and order by priority, that will give you cleaner solution. You can even create temporary table and index by value if there are a lot of lookup values, to ensure speed is appropriate.

然后在值列上连接到子查询并按优先级排序，这将为您提供更清晰的解决方案。如果有大量查找值，您甚至可以按值创建临时表和索引，以确保速度合适。

#1