SQL 排序问题

MDevelop 2012-04-24 06:18:44

在开发过程中有如下排序问题，还请高手帮忙解决。
有如下数据：
A
B
C
A,B
A,B,C
A,B,C,E
A,B,C,F
A,B,E,F
A,B,F
A,B,F,N
A,B,F,Y
A,C
A,C,E
A,C,F
A,E
A,E,F
A,F
A,F,G
A,N
B,C
B,C,E
B,E
B,F
B,G
B,N
B,P
C,E
C,F
C,N
E,G
E,G,N
F,G
F,N
N,P,Q
N,Q

排序规则如下：
先排(A),然后(B)，再出(A,B)，(C)，(A,B,C)，(A,C)……

...全文

108 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

simonxt 2012-04-25

打赏
举报

8楼正解
“如果每个name里的字母不是按从小到大的顺序排好序的”，那就先写个函数处理好再排序。

junshanhudazhaxi 2012-04-25

打赏
举报

-- 创建测试数据临时表

create table #t (name varchar(50));



-- 插入测试数据

insert into #t 

select 'A'

union all select 'B'

union all select 'C'

union all select 'A,B'

union all select 'A,B,C'

union all select 'A,B,C,E'

union all select 'A,B,C,F'

union all select 'A,B,E,F'

union all select 'A,B,F'

union all select 'A,B,F,N'

union all select 'A,B,F,Y'

union all select 'A,C'

union all select 'A,C,E'

union all select 'A,C,F'

union all select 'A,E'

union all select 'A,E,F'

union all select 'A,F'

union all select 'A,F,G'

union all select 'A,N'

union all select 'B,C'

union all select 'B,C,E'

union all select 'B,E'

union all select 'B,F'

union all select 'B,G'

union all select 'B,N'

union all select 'B,P'

union all select 'C,E'

union all select 'C,F'

union all select 'C,N'

union all select 'E,G'

union all select 'E,G,N'

union all select 'F,G'

union all select 'F,N'

union all select 'N,P,Q'

union all select 'N,Q';



-- 查询数据，按ascii码排序

--(注意这里是假设每个name里的字母都是按从小到大的顺序排好序的)

select * from #t order by REVERSE(name);