日期:2014-05-18  浏览次数:20453 次

有关一条查询语句
create table #temp10
(ddbh varchar(32),
cpbh varchar(32)
)

insert into #temp10
values('D1106-08774','110241001')

insert into #temp10
values('D1106-08774','120111001')

insert into #temp10
values('D1106-08775','120111001')


D1106-08774 1 110241001
D1106-08774 2 120111001
D1106-08775 1 120111001
结果

------解决方案--------------------
SQL code

--2000

select *,px=identity(int,1,1)  --不好意思,字打错了。identity
    into #tb
from #temp10

select ddbh,cpbh,rid=(select count(*) from #tb where px <= t.px)
from #tb t

------解决方案--------------------
SQL code
select
 *,no=(select COUNT(1) from #temp10 where ddbh=a.ddbh and cpbh<=a.cpbh)
from
 #temp10 a

------解决方案--------------------
SQL code
create table #temp10
(ddbh varchar(32),
cpbh varchar(32)
)

insert into #temp10
values('D1106-08774','110241001')

insert into #temp10
values('D1106-08774','120111001')

insert into #temp10
values('D1106-08775','120111001')

select ddbh,(select count(*) from #temp10 a 
             where a.ddbh=#temp10.ddbh 
            and a.cpbh<=#temp10.cpbh) as id,cpbh 
from #temp10

/*
ddbh                             id          cpbh         
-------------------------------- ----------- --------------
D1106-08774                      1           110241001
D1106-08774                      2           120111001
D1106-08775                      1           120111001

(所影响的行数为 3 行)
*/