有关一条查询语句
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 1110241001
D1106-08774 2120111001
D1106-08775 1120111001
结果
[解决办法]
--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
select
*,no=(select COUNT(1) from #temp10 where ddbh=a.ddbh and cpbh<=a.cpbh)
from
#temp10 a
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 行）
*/
