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

[primulaling]

表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).依次类推

[playwarcraft]

我想到的辦法是，
建一個臨時表#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怎樣

[-狙击手-]

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 行）
*/

[lhsxsh]

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

[dawugui]

我的认为是:我没看懂.

[primulaling]

[Quote=引用 4 楼 dawugui 的回复:]
我的认为是:我没看懂.
[/Quote]

诶，我没有讲清楚吗？
你的程序在我这里已经通过了，我正准备结题那

[dawugui]

[Quote=引用 5 楼 primulaling 的回复:]
引用 4 楼 dawugui 的回复:
我的认为是:我没看懂.


诶，我没有讲清楚吗？
你的程序在我这里已经通过了，我正准备结题那
[/Quote]
好象和上次的不一样了?
那我再看看.

[dawugui]

--看懂了.
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 行）
*/

[primulaling]

谢谢大家的帮助。结题了