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

bsd 2004-09-17 04:40:14

有表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该如何写？
多谢了

...全文

255 16 点赞打赏收藏举报

写回复

16 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

bsd 2004-09-23

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

打赏
举报

回复赞

lsxaa 2004-09-19

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

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

打赏
举报

回复赞

lsxaa 2004-09-19

参考上面的,给出一个不用游标的
--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 2004-09-19

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

打赏
举报

回复赞

icevi 2004-09-18

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

打赏
举报

回复赞

zjcxc 2004-09-18

分析按钮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 2004-09-18

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