表1col1 col2
exon 12001.m06748
exon 12001.m06748
exon 12001.m06748
exon 12001.m06748
exon 12001.m42814
exon 12001.m42814
exon 12001.m42814
exon 12001.m42814
exon 12001.m15093
exon 12001.m15093
exon 12001.m06750
exon 12001.m06750
exon 12001.m06750 结果是
表2
col1 col2
exon1 12001.m06748
exon2 12001.m06748
exon3 12001.m06748
exon4 12001.m06748
exon1 12001.m42814
exon2 12001.m42814
exon3 12001.m42814
exon1 12001.m15093
exon2 12001.m15093
exon1 12001.m06750
exon2 12001.m06750
exon3 12001.m06750
就是根据col2中的每个组对col1中的分别进行排序
exon 12001.m06748
exon 12001.m06748
exon 12001.m06748
exon 12001.m06748
exon 12001.m42814
exon 12001.m42814
exon 12001.m42814
exon 12001.m42814
exon 12001.m15093
exon 12001.m15093
exon 12001.m06750
exon 12001.m06750
exon 12001.m06750 结果是
表2
col1 col2
exon1 12001.m06748
exon2 12001.m06748
exon3 12001.m06748
exon4 12001.m06748
exon1 12001.m42814
exon2 12001.m42814
exon3 12001.m42814
exon1 12001.m15093
exon2 12001.m15093
exon1 12001.m06750
exon2 12001.m06750
exon3 12001.m06750
就是根据col2中的每个组对col1中的分别进行排序
不是这样的,col2中显示的信息是一组一组的信息
是想将col1中的数据在每个独立的组内排序。
不知道讲清楚了没
from tbselect col1+ltrim(select count(1) from # where col2 = a.col2 and px < a.px),
col2
from #drop table #
select * from tb order by col2 , col1
sql server 2000只能利用临时表来排序.
select * , px = identity(int,1,1) into tmp from tb
select col1 + cast(px1 as varchar) as col1,col2 from
(
select col1,col2,px1 = (select count(1) from tmp where col1 = t.col1 and col2 = t.col2 and px < t.px) from tmp t
) msql server 2005可以利用row_number.
select col1 + cast(px1 as varchar) as col1,col2 from
(
select col1,col2,px1 = row_number() over(order by col1,col2) from tb
) m
create table tb(col1 varchar(10) , col2 varchar(20))
insert into tb values('exon', '12001.m06748')
insert into tb values('exon', '12001.m06748')
insert into tb values('exon', '12001.m06748')
insert into tb values('exon', '12001.m06748')
insert into tb values('exon', '12001.m42814')
insert into tb values('exon', '12001.m42814')
insert into tb values('exon', '12001.m42814')
insert into tb values('exon', '12001.m42814')
insert into tb values('exon', '12001.m15093')
insert into tb values('exon', '12001.m15093')
insert into tb values('exon', '12001.m06750')
insert into tb values('exon', '12001.m06750')
insert into tb values('exon', '12001.m06750')
goselect * , px = identity(int,1,1) into tmp from tb
select col1 + cast(px1 as varchar) as col1,col2 from
(
select col1,col2,px1 = (select count(1) from tmp where col1 = t.col1 and col2 = t.col2 and px < t.px) + 1 from tmp t
) m drop table tb ,tmp/*
col1 col2
---------------------------------------- --------------------
exon1 12001.m06748
exon2 12001.m06748
exon3 12001.m06748
exon4 12001.m06748
exon1 12001.m42814
exon2 12001.m42814
exon3 12001.m42814
exon4 12001.m42814
exon1 12001.m15093
exon2 12001.m15093
exon1 12001.m06750
exon2 12001.m06750
exon3 12001.m06750(所影响的行数为 13 行)
*/
create table tb(col1 varchar(10) , col2 varchar(20))
insert into tb values('exon', '12001.m06748')
insert into tb values('exon', '12001.m06748')
insert into tb values('exon', '12001.m06748')
insert into tb values('exon', '12001.m06748')
insert into tb values('exon', '12001.m42814')
insert into tb values('exon', '12001.m42814')
insert into tb values('exon', '12001.m42814')
insert into tb values('exon', '12001.m42814')
insert into tb values('exon', '12001.m15093')
insert into tb values('exon', '12001.m15093')
insert into tb values('exon', '12001.m06750')
insert into tb values('exon', '12001.m06750')
insert into tb values('exon', '12001.m06750')
goselect col1 + cast(px1 as varchar) as col1,col2 from
(
select col1,col2,px1 = (select count(1) from
(
select col1,col2,px = row_number() over(order by col1,col2) from tb
) m where m.col1 = n.col1 and m.col2 = n.col2 and m.px < n.px) + 1 from
(
select col1,col2,px = row_number() over(order by col1,col2) from tb
) n
) tdrop table tb/*
col1 col2
---------------------------------------- --------------------
exon1 12001.m06748
exon2 12001.m06748
exon3 12001.m06748
exon4 12001.m06748
exon1 12001.m06750
exon2 12001.m06750
exon3 12001.m06750
exon1 12001.m15093
exon2 12001.m15093
exon1 12001.m42814
exon2 12001.m42814
exon3 12001.m42814
exon4 12001.m42814(13 行受影响)
*/
from tb
from tb
select'exon', '12001.m06748' union all
select 'exon', '12001.m06748' union all
select 'exon', '12001.m06748' union all
select 'exon', '12001.m06748'
select px=identity(int ,1,1) ,* into #1 from table1
select col1+convert(varchar,(select count(1) from #1 where col2=a.col2 and px< =a.px)) ,col2 from #1 a
drop #1