我现在有一个表,之前的行号是错误的,我现在想重新对这个行号进行排序更新。在更新的时候,需要考虑到BOMID这个字段,排序依据ITEMID这个这段,怎样才能将下列的原表更新成表二那样?
(因为涉及几万行,将这个导出来到EXCEL整理,再重新更新到这个表工作量实在是太大。)原表LINENUM BOMID ITEMID
1 铅笔-1 40-065-07AP98
1 铅笔-1 40-130-04AB90
1 铅笔-1 61-03-0601901 #1356抽粒 83-PP-7675KE2
1 #1356抽粒 84-03-1356-S1 #1356抽粒-I 83-PP-7675KE2-I
1 #1356抽粒-I 84-03-1356-I-S1 #AP215863拉粒 83-ABS-PA777D
1 #AP215863拉粒 84-03-AP215863-S
1 #AP215863拉粒 83-ABS-PA777D-I
1 #AP215863拉粒 84-03-AP215863-I-S1 #C5001 83-ABS-PA777D
1 #C5001 84-02-G023
1 #C5001 84-02-M2
1 #C5001 84-03-C5001-S
1 #C5001 83-ABS-PA777D-I
……更新成:LINENUM BOMID ITEMID
1 铅笔-1 40-065-07AP98
2 铅笔-1 40-130-04AB90
3 铅笔-1 61-03-0601901 #1356抽粒 83-PP-7675KE2
2 #1356抽粒 84-03-1356-S1 #1356抽粒-I 83-PP-7675KE2-I
2 #1356抽粒-I 84-03-1356-I-S1 #AP215863拉粒 83-ABS-PA777D
2 #AP215863拉粒 84-03-AP215863-S
3 #AP215863拉粒 83-ABS-PA777D-I
4 #AP215863拉粒 84-03-AP215863-I-S1 #C5001 83-ABS-PA777D
2 #C5001 84-02-G023
3 #C5001 84-02-M2
4 #C5001 84-03-C5001-S
5 #C5001 83-ABS-PA777D-I
……
(因为涉及几万行,将这个导出来到EXCEL整理,再重新更新到这个表工作量实在是太大。)原表LINENUM BOMID ITEMID
1 铅笔-1 40-065-07AP98
1 铅笔-1 40-130-04AB90
1 铅笔-1 61-03-0601901 #1356抽粒 83-PP-7675KE2
1 #1356抽粒 84-03-1356-S1 #1356抽粒-I 83-PP-7675KE2-I
1 #1356抽粒-I 84-03-1356-I-S1 #AP215863拉粒 83-ABS-PA777D
1 #AP215863拉粒 84-03-AP215863-S
1 #AP215863拉粒 83-ABS-PA777D-I
1 #AP215863拉粒 84-03-AP215863-I-S1 #C5001 83-ABS-PA777D
1 #C5001 84-02-G023
1 #C5001 84-02-M2
1 #C5001 84-03-C5001-S
1 #C5001 83-ABS-PA777D-I
……更新成:LINENUM BOMID ITEMID
1 铅笔-1 40-065-07AP98
2 铅笔-1 40-130-04AB90
3 铅笔-1 61-03-0601901 #1356抽粒 83-PP-7675KE2
2 #1356抽粒 84-03-1356-S1 #1356抽粒-I 83-PP-7675KE2-I
2 #1356抽粒-I 84-03-1356-I-S1 #AP215863拉粒 83-ABS-PA777D
2 #AP215863拉粒 84-03-AP215863-S
3 #AP215863拉粒 83-ABS-PA777D-I
4 #AP215863拉粒 84-03-AP215863-I-S1 #C5001 83-ABS-PA777D
2 #C5001 84-02-G023
3 #C5001 84-02-M2
4 #C5001 84-03-C5001-S
5 #C5001 83-ABS-PA777D-I
……
-->Microsoft SQL Server Management Studio Complete 2008***
-->AUTHOR : Mr wang **********
-->CREATE TIME : 2010-11-18 16:09:27 **************
-->*******************************************************
--> 测试数据:[TB]
if object_id('[TB]') is not null
drop table [TB]---->建表
create table [TB]([LINENUM] int,[BOMID] varchar(13),[ITEMID] varchar(18))
insert [TB]
select 1,'铅笔-1','40-065-07AP98' union all
select 1,'铅笔-1','40-130-04AB90' union all
select 1,'铅笔-1','61-03-060190' union all
select 1,'1356抽粒','83-PP-7675KE2' union all
select 1,'1356抽粒','84-03-1356-S' union all
select 1,'1356抽粒-I','83-PP-7675KE2-I' union all
select 1,'1356抽粒-I','84-03-1356-I-S' union all
select 1,'AP215863拉粒','83-ABS-PA777D' union all
select 1,'AP215863拉粒','84-03-AP215863-S' union all
select 1,'AP215863拉粒','83-ABS-PA777D-I' union all
select 1,'AP215863拉粒','84-03-AP215863-I-S' union all
select 1,'C5001','83-ABS-PA777D' union all
select 1,'C5001','84-02-G023' union all
select 1,'C5001','84-02-M2' union all
select 1,'C5001','84-03-C5001-S' union all
select 1,'C5001','83-ABS-PA777D-I'--> 查询结果
with th AS(
SELECT * ,px=ROW_NUMBER()over (PARTITION by [BOMID] order by [ITEMID])
FROM [TB]
)UPDATE th
set [LINENUM]=px
go
SELECT * FROM [TB]--> 删除表格
DROP TABLE [TB]
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (LINENUM int,BOMID varchar(11),ITEMID varchar(15))
insert into #tb
select 1,'铅笔-1','40-065-07AP98' union all
select 1,'铅笔-1','40-130-04AB90' union all
select 1,'铅笔-1','61-03-060190' union all
select 1,'#1356抽粒','83-PP-7675KE2' union all
select 1,'#1356抽粒','84-03-1356-S' union all
select 1,'#1356抽粒-I','83-PP-7675KE2-I' union all
select 1,'#1356抽粒-I','84-03-1356-I-S'alter table #tb add id int identity(1,1)update #tb
set LINENUM=(select count(*) from #tb where BOMID=t.BOMID and id<=t.id)
from #tb t
select * from #tb
LINENUM BOMID ITEMID id
----------- ----------- --------------- -----------
1 铅笔-1 40-065-07AP98 1
2 铅笔-1 40-130-04AB90 2
3 铅笔-1 61-03-060190 3
1 #1356抽粒 83-PP-7675KE2 4
2 #1356抽粒 84-03-1356-S 5
1 #1356抽粒-I 83-PP-7675KE2-I 6
2 #1356抽粒-I 84-03-1356-I-S 7(7 行受影响)
update tb set LINENUM=row_number()over(partition by BOMID order by ITEMID)
update tb set LINENUM = (select count(1)+1 from tb where BOMID = t.BOMID and ITEMID < t.ITEMID) from tb t
insert into tb
select 1,'铅笔-1','40-065-07AP98' union all
select 1,'铅笔-1','40-130-04AB90' union all
select 1,'铅笔-1','61-03-060190' union all
select 1,'#1356抽粒','83-PP-7675KE2' union all
select 1,'#1356抽粒','84-03-1356-S' union all
select 1,'#1356抽粒-I','83-PP-7675KE2-I' union all
select 1,'#1356抽粒-I','84-03-1356-I-S'update tb set LINENUM = (select count(1) from tb where BOMID = t.BOMID and ITEMID < t.ITEMID) + 1 from tb tselect * from tbdrop table tb/*LINENUM BOMID ITEMID
----------- ----------- ---------------
1 铅笔-1 40-065-07AP98
2 铅笔-1 40-130-04AB90
3 铅笔-1 61-03-060190
1 #1356抽粒 83-PP-7675KE2
2 #1356抽粒 84-03-1356-S
1 #1356抽粒-I 83-PP-7675KE2-I
2 #1356抽粒-I 84-03-1356-I-S(所影响的行数为 7 行)*/
update t set LINENUM =
(select count(1)+1 from tb
where BOMID = t.BOMID and ITEMID < t.ITEMID)
from tb t