如何实现查询出来的数据重新编号

Question

table1
id      s10bh      s10mc          re       cplx
1       A1002       A合金包                          CP
2       A1003       书页             红色            BCP
3       INT006      白面              白色            BCP  
4       A1005       书页                             CP  
5       A2003       书页                             BCP  
6       A2008       新闻纸08                         WL  
7       A3002       素材05             单色          CP  
8       MM18        书页               红色          CP      我想查询
table2 CPLX='CP'的记录  ,编号能够重新编排,结果是
id     itemno       s10bh       s10mc            re     cplx
1       1           A1002       A合金包                          CP
4       2           A1005       书页                             CP  
7       3           A3002       素材05             单色        CP  
8       4            MM18        书页               红色          CP 谢谢 

Answer 1

--> liangCK小梁 于2008-09-06
--> 生成测试数据: #table1
if object_id('tempdb.dbo.#table1') is not null drop table #table1
create table #table1 (id int,s10bh nvarchar(6),s10mc nvarchar(8),re nvarchar(4),cplx varchar(3))
insert into #table1
select 1,'A1002','A合金包',null,'CP' union all
select 2,'A1003','书页','红色','BCP' union all
select 3,'INT006','白面','白色','BCP' union all
select 4,'A1005','书页',null,'CP' union all
select 5,'A2003','书页',null,'BCP' union all
select 6,'A2008','新闻纸08',null,'WL' union all
select 7,'A3002','素材05','单色','CP' union all
select 8,'MM18','书页','红色','CP'select id,
       itemid=row_number() over(order by id)
       , s10bh,s10mc,re,cplx 
from #table1 t
where cplx='CP'/*
id          itemid               s10bh  s10mc    re cplx
----------- -------------------- ------ -------- ------ ----
1           1                    A1002  A合金包     NULL   CP
4           2                    A1005  书页       NULL   CP
7           3                    A3002  素材05     单色     CP
8           4                    MM18   书页       红色     CP(4 行受影响)
*/

Answer 2

select ID*1,identity(int,1,1)itemno,s10bh ,s10mc ,re ,cplx into table2 from table where CPLX='CP'

Answer 3

--> liangCK小梁 于2008-09-06
--> 生成测试数据: #table1
if object_id('tempdb.dbo.#table1') is not null drop table #table1
create table #table1 (id int,s10bh nvarchar(6),s10mc nvarchar(8),re nvarchar(4),cplx varchar(3))
insert into #table1
select 1,'A1002','A合金包',null,'CP' union all
select 2,'A1003','书页','红色','BCP' union all
select 3,'INT006','白面','白色','BCP' union all
select 4,'A1005','书页',null,'CP' union all
select 5,'A2003','书页',null,'BCP' union all
select 6,'A2008','新闻纸08',null,'WL' union all
select 7,'A3002','素材05','单色','CP' union all
select 8,'MM18','书页','红色','CP'select id,
       itemid=(select count(*) from #table1 where cplx='CP' and id<t.id)+1
       , s10bh,s10mc,re,cplx 
from #table1 t
where cplx='CP'/*
id          itemid      s10bh  s10mc    re cplx
----------- ----------- ------ -------- ------ ----
1           1           A1002  A合金包     NULL   CP
4           2           A1005  书页       NULL   CP
7           3           A3002  素材05     单色     CP
8           4           MM18   书页       红色     CP(4 行受影响)
*/

Answer 4

如果是2005直接用 row_number()函数即可。row_number() over(order by id)