MyException - 我的异常网
当前位置:我的异常网» Sql Server » 关于自动编号方面的有关问题!

关于自动编号方面的有关问题!(2)

www.MyException.Cn  网友分享于:2013-12-31  浏览:8次


insert @tab values(7, 'aa ',153)
insert @tab values(9, 'aa ',152)
insert @tab values(6, 'aa ',120)
insert @tab values(8, 'aa ',168)
insert @tab values(5, 'bb ',159)
insert @tab values(7, 'bb ',179)
insert @tab values(8, 'bb ',149)
insert @tab values(9, 'bb ',139)
insert @tab values(6, 'bb ',169)

select * from @tab

select px=(select count(1) from @tab where b=a.b and c> a.c)+1 , a,b,c from @tab a
order by b , c desc

px a b c
----------- ----------- ---- -----------
1 8 aa 168
2 7 aa 153
3 9 aa 152
4 6 aa 120
1 7 bb 179
2 6 bb 169
3 5 bb 159
4 8 bb 149
5 9 bb 139

(所影响的行数为 9 行)


------解决方案--------------------
create table table5(col1 varchar(02))
insert into table5
select 'a '
insert into table5
select 'a '
insert into table5
select 'b '
insert into table5
select 'b '
insert into table5
select 'c '
insert into table5
select 'c '
insert into table5
select 'c '

select id=identity(int,1,1),col1 into #t from table5 order by col1

select no=(select count(*) from #t b where b.col1=a.col1 and b.id <=a.id),col1
from #t a

drop table #t,table5

no col1
----------- ----
1 a
2 a
1 b
2 b
1 c
2 c
3 c
------解决方案--------------------
--如:
Select *,identity(int,1,1) as ID into #t from (select col= 'a '
union all select 'a '
union all select 'b '
union all select 'b '
union all select 'b '
union all select 'c '
union all select 'c ') as t

Select no=(Select count(*) from #t where col=a.col and id <=a.id),col
from #t as a

drop table #t
------解决方案--------------------
--sql 2005
SELECT ROW_NUMBER() OVER(
PARTITION BY classid
ORDER BY classid asc) AS pos,
classid
FROM table
ORDER BY classid asc

文章评论

软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有