求助一个关于数据排序的SQL的写法

[bsd]

有表tblA
id colA colB
1 A1 A2
2 A2 A3
3 A2 A4
4 A4 A3
5 A5 A1
6 A5 A2
7 A5 A3
其中
colA与colB中的值都来自另一张表的同一个字段
现在需要对这些数据排序输出
规则是
对于表中的一条纪录，colB的值必须列在colA的前面
最终输出的结果不能重复
针对示例中的数据结果应为：
A3
A4
A2
A1
A5

请问实现该效果的sql该如何写？
多谢了

[lsxaa]

没看明白 楼主的结果和楼主说的好像不一样

[ywh25]

没看懂

[chinaandys]

看不明，是不是这样啊:

select * from 表 order by colA desc,colB asc

[zjcxc]

看不明白.

[zjcxc]

结果不得少了记录,还少了字段,根本看不出与原表的关系.

[icedut]

现在好像是比较流行“含蓄的文问题”

[bsd]

不好意思，这么多朋友没看懂
看来是我的错了
再解释一番

该结果是合并了tblA表中的colA与colB的所有数据（成一列）
经过排序而成的
输出的结果之所以只有5个
是因为前面有个限制条件“结果不能重复”
谢谢大家

[zjcxc]

还中看不懂.

[icevi]

哈哈，我看懂了，有点意思。做个记号，等我睡好觉了再来想想：）

[icevi]

set nocount on

select identity(int,1,1) as findex,cola as fval into #t
from (select distinct cola from (select cola as cola from tbla union all select colb from tbla) t1) t5

select findex+0 as findex,fval into #t1 from #t

DECLARE cur CURSOR
READ_ONLY
FOR SELECT * FROM tbla order by id

DECLARE @id int
declare @cola varchar(10)
declare @colb varchar(10)

OPEN cur

FETCH NEXT FROM cur INTO @id,@cola,@colb
WHILE (@@fetch_status <> -1)
BEGIN
if (select findex from #t1 where #t1.fval=@colb)>(select findex from #t1 where #t1.fval=@cola)
begin --需要调整顺序了
update #t1 set findex=findex+1 where findex between
(select findex from #t1 where #t1.fval=@cola) and
(select findex from #t1 where #t1.fval=@colb)-1
update #t1 set findex=findex-@@rowcount where fval=@colb
end
FETCH NEXT FROM cur INTO @id,@cola,@colb
END

CLOSE cur
DEALLOCATE cur
GO

set nocount off

--得到结果
findex fval
----------- ----------
1 A3
2 A4
3 A2
4 A1
5 A5

select * from #t1 order by findex

[zjcxc]

分析按钮JJ的,算是看明白楼主的要求了.

对上面的处理提出个人的改进意见(还是用游标,处理核心方法没有改变)

--生成处理的临时表
select findex=0,fval=cola into #t from tbla union select 0,colb from tbla
declare @i int
set @i=0
update #t set @i=@i+1,findex=@i

--定义处理游标,只对满足条件的部分进行顺序调整
declare @id1 int,@id2 int,@colb varchar(10)
declare tb cursor read_only local
for select t1.findex,t2.findex-1,a.colb
from #t t1,#t t2,tblA a
where a.colA=t1.fval
and a.colB=t2.fval
and t2.findex>t1.findex

open tb
fetch tb into @id1,@id2,@colb
while @@fetch_status=0
begin
update #t set findex=findex+1
where findex between @id1 and @id2
update #t set findex=findex-@@rowcount
where fval=@colb
fetch tb into @id1,@id2,@colb
end
close tb
deallocate tb

--显示结果
select fval from #t order by findex

[icevi]

嗯，这样更好些，谢谢楼上的了：）

[lsxaa]

icevi(按钮工厂) 理解能力好强,佩服

[lsxaa]

参考上面的,给出一个不用游标的
--findex 是代表级别，级别越高，数值越高

select findex=0,fval=cola into #t from tbla union select 0,colb from tbla
update #t set findex=(select count(*) from #t) --赋初值在相同级别上

update #t set findex=findex-(select count(*) from tbla where colA=#t.fval)
update #t set findex=findex+(select count(*) from tbla where colB=#t.fval)

update #t set findex=findex+0.1*(select count(*) from #t b
where b.findex=#t.findex
and exists(select 1 from tbla
where tbla.colA=#t.fval
and tabla.colB=b.fval )
)

select fval from #t order by findex desc

[lsxaa]

最后一句有点错误:
重新改一下

select findex=0,fval=cola into #t from tbla union select 0,colb from tbla
update #t set findex=(select count(*) from #t) --赋初值在相同级别上

update #t set findex=findex-(select count(*) from tbla where colA=#t.fval)
update #t set findex=findex+(select count(*) from tbla where colB=#t.fval)

update #t set findex=findex+0.1*(select count(*) from #t b
where b.findex=#t.findex
and exists(select 1 from tbla
where tbla.colA=b.fval
and tabla.colB=#t.fval )
) --修改位置

select fval from #t order by findex desc

[bsd]

都是高人！
谁能帮偶解释一下思路？//shy
多谢了！