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

2008-03-14 12:30:38 118 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

C语言：随机生成9个正整数放在数组a中，按升学排序后生成数组b,找出b数组处于中间位置的数，将原数组a中比该中间数小的数用该中间数替换，但位置不变。 C语言：随机生成9个正整数放在数组a中，按升学排序后生成数组b,找出b数组处于中间位置的数，将原数组a中比该中间数小的数用该中间数替换，但位置不变。阅读详情

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

...全文

从官方例子入手（详解排序、分组）——kettle实战（3） kettle 排序分组一生成记录二生成随机数据三排序记录四排序合并五分组六组序号七替换八写入txt 阅读详情

primulaling

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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怎樣

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

相关推荐