组内中间数据的排序和替换问题

primulaling 2008-03-14 12:30:38
表1
col1 start end col4
exon1 17 57 12001.m06748
exon2 156 418 12001.m06748
exon3 1159 1257 12001.m06748
exon1 9450 10615 12001.m06750
exon2 10708 11073 12001.m06750
exon3 11161 11239 12001.m06750
exon4 11771 11973 12001.m06750
exon1 19643 20083 12001.m06752
exon2 20374 20649 12001.m06752
exon3 20764 20835 12001.m06752
exon4 21294 21379 12001.m06752


结果表2
col1 start end col4
ray1 58 155 12001.m06748
ray2 419 1158 12001.m06748
ray1 10616 10707 12001.m06750
ray2 11074 11160 12001.m06750
ray3 11240 11770 12001.m06750
ray1 20084 20373 12001.m06752
ray2 20650 20763 12001.m06752
ray3 20836 21293 12001.m06752
是想将表1的exon替换成ray,并对ray排序。其中ray的排序是由每组内(以col4列分组)exon的start和end值来确定
规律是:以12001.m06748 组为例,exon1+ray1+exon2+ray2+exon3 其中 ray1(start)=exon1(end+1), ray1(end)=exon2(start-1).依次类推

...全文
50 点赞 收藏 8
写回复
8 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
primulaling 2008-03-16
谢谢大家的帮助。结题了
回复
dawugui 2008-03-16
--看懂了.
create table ta(col1 varchar(10),start int,[end] int,col4 varchar(20))
insert into ta select
'exon1', 17 , 57 ,'12001.m06748' union select
'exon2', 156 , 418 ,'12001.m06748' union select
'exon3', 1159 , 1257 ,'12001.m06748' union select
'exon1', 9450 , 10615 ,'12001.m06750' union select
'exon2', 10708 , 11073 ,'12001.m06750' union select
'exon3', 11161 , 11239 ,'12001.m06750' union select
'exon4', 11771 , 11973 ,'12001.m06750' union select
'exon1', 19643 , 20083 ,'12001.m06752' union select
'exon2', 20374 , 20649 ,'12001.m06752' union select
'exon3', 20764 , 20835 ,'12001.m06752' union select
'exon4', 21294 , 21379 ,'12001.m06752'
go

select col1 = 'ray' + cast(m.px as varchar) ,
start = m.[end] + 1 ,
[end] = n.start - 1 ,
m.col4 from
(select * , px = (select count(1) from ta where col4 = t.col4 and start < t.start ) + 1 from ta t) m,
(select * , px = (select count(1) from ta where col4 = t.col4 and start < t.start ) + 1 from ta t) n
where m.col4 = n.col4 and m.px = n.px - 1
order by m.col4 , col1

drop table ta

/*
col1 start end col4
--------------------------------- ----------- ----------- --------------------
ray1 58 155 12001.m06748
ray2 419 1158 12001.m06748
ray1 10616 10707 12001.m06750
ray2 11074 11160 12001.m06750
ray3 11240 11770 12001.m06750
ray1 20084 20373 12001.m06752
ray2 20650 20763 12001.m06752
ray3 20836 21293 12001.m06752

(所影响的行数为 8 行)
*/
回复
dawugui 2008-03-16
[Quote=引用 5 楼 primulaling 的回复:]
引用 4 楼 dawugui 的回复:
我的认为是:我没看懂.


诶,我没有讲清楚吗?
你的程序在我这里已经通过了,我正准备结题那
[/Quote]
好象和上次的不一样了?
那我再看看.
回复
primulaling 2008-03-16
[Quote=引用 4 楼 dawugui 的回复:]
我的认为是:我没看懂.
[/Quote]

诶,我没有讲清楚吗?
你的程序在我这里已经通过了,我正准备结题那
回复
dawugui 2008-03-15
我的认为是:我没看懂.
回复
lhsxsh 2008-03-15
create table ta(col1 varchar(10),start int,[end] int,col4 varchar(20))
insert into ta select
'exon1', 17 , 57 ,'12001.m06748' union select
'exon2', 156 , 418 ,'12001.m06748' union select
'exon3', 1159 , 1257 ,'12001.m06748' union select
'exon1', 9450 , 10615 ,'12001.m06750' union select
'exon2', 10708 , 11073 ,'12001.m06750' union select
'exon3', 11161 , 11239 ,'12001.m06750' union select
'exon4', 11771 , 11973 ,'12001.m06750' union select
'exon1', 19643 , 20083 ,'12001.m06752' union select
'exon2', 20374 , 20649 ,'12001.m06752' union select
'exon3', 20764 , 20835 ,'12001.m06752' union select
'exon4', 21294 , 21379 ,'12001.m06752'

go

select *
from (
select stuff(col1,1,4,'ray') as col1,
start=(select [end] +1 from ta where a.col1 = col1 and col4 = a.col4),
[end]=(select [start] - 1 from ta where cast(stuff(a.col1,1,4,'') as int) = cast(stuff(col1,1,4,'') as int)-1 and col4 = a.col4),
col4
from ta a) b
where [end] is not null
order by col4,col1
drop table ta

回复
-狙击手- 2008-03-14
create table ta(col1 varchar(10),start int,[end] int,col4   varchar(20))
insert into ta select
'exon1', 17 , 57 ,'12001.m06748' union select
'exon2', 156 , 418 ,'12001.m06748' union select
'exon3', 1159 , 1257 ,'12001.m06748' union select
'exon1', 9450 , 10615 ,'12001.m06750' union select
'exon2', 10708 , 11073 ,'12001.m06750' union select
'exon3', 11161 , 11239 ,'12001.m06750' union select
'exon4', 11771 , 11973 ,'12001.m06750' union select
'exon1', 19643 , 20083 ,'12001.m06752' union select
'exon2', 20374 , 20649 ,'12001.m06752' union select
'exon3', 20764 , 20835 ,'12001.m06752' union select
'exon4', 21294 , 21379 ,'12001.m06752'

go

select *
from (
select stuff(col1,1,4,'ray') as col1,
start=(select [end] +1 from ta where a.col1 = col1 and col4 = a.col4),
[end]=(select [start] - 1 from ta where cast(stuff(a.col1,1,4,'') as int) = cast(stuff(col1,1,4,'') as int)-1 and col4 = a.col4),
col4
from ta a) b
where [end] is not null
order by col4,col1
drop table ta

/*
col1 start end col4
--------- ----------- ----------- --------------------
ray1 58 155 12001.m06748
ray2 419 1158 12001.m06748
ray1 10616 10707 12001.m06750
ray2 11074 11160 12001.m06750
ray3 11240 11770 12001.m06750
ray1 20084 20373 12001.m06752
ray2 20650 20763 12001.m06752
ray3 20836 21293 12001.m06752

(所影响的行数为 8 行)
*/
回复
playwarcraft 2008-03-14
我想到的辦法是,
建一個臨時表#t,
id
---
17
18
19
20
...
21379

然后利用
select * from #t left join 表1 on start>=id and end<=id
where 表1.col1 is null

然后將上面的結果再處理就簡單了.
不過要是max(end)比較大的話,建臨時表不知道處理起來performance怎樣
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-14 12:30
社区公告
暂无公告