在 SQL Server 2000 中按字段按非字母顺序排序 [英] Order by Field in non-alphabetical order in SQL Server 2000

问题描述

我正在尝试按不按字母顺序排列的名称列表对项目进行排序.完成列表后，我尝试按字母顺序继续其余部分，而不是我最初选择的那些.

I'm trying to order items by a list of names that are not in alphabetical order. After completing the list I am trying to continue the rest in alphabetical order without the ones I initially selected.

参见示例:

输入:

print 'Results:'  
select * from Geniuses
    order by ('Charles Babbage', 
              'Albert Einstein', 
              'Adrien-Marie Legendre', 
              'Niels Henrik Abel')  

然后最后按字母顺序对其余部分进行排序...

then finally sort the rest in alphabetical order...

输出:

Results:
Charles Babbage ... details
Albert Einstein ...
Adrien-Marie Legendre ...
Niels Henrik Abel ...
Arthur Cayley ...
...

推荐答案

select * from Geniuses
order by
    -- First, order by your set order...
    case FullName
        when 'Charles Babbage' then 1
        when 'Albert Einstein' then 2
        when 'Adrien-Marie Legendre' then 3
        when 'Niels Henrik Abel' then 4
        else 5 
    end,
    -- Then do a secondary sort on FullName for everyone else.
    FullName

我看到您的评论，每个用户都可以对其进行配置.在这种情况下，您必须有一个 FavoriteGeniuses 表来跟踪哪个用户喜欢哪个 Geniuses，然后在该表中指定一个排序顺序:

I saw your comment that it's configurable by each user. In that case, you'd have to have a FavoriteGeniuses table that tracks which user prefers which Geniuses, and then have a sort order specified in that table:

select * 
from 
    Geniuses g left join
    FavoriteGeniuses fg 
        ON fg.GeniusID = g.GeniusID 
        AND fg.UserID = @UserID
order by
    -- The higher the number, the first up on the list.
    -- This will put the NULLs (unspecified) below the favorites.
    fg.SortPriority DESC, 
    f.FullName

这篇关于在 SQL Server 2000 中按字段按非字母顺序排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！