SQL2000分组后再按大小排序更新

Question

SQL2000分组后再按大小排序更新
(按mxitemno 分组，再按itemno的大小重新排序)  更新到xitemno
表结构如下:
billid,itemno,mxitemno,materialid,quantity     ,xitemno
145 1 1 110 585.0000000000 ,0
145 2 1 111 270.0000000000 ,0
145 3 1 112 45.00000000000 ,0
145 4 2 52 800.0000000000 ,0
145 5 2 55 800.0000000000 ,0
145 6 2 63 800.0000000000 ,0
145 7 2 82 800.0000000000 ,0
145 8 2 59 800.0000000000 ,0
145 9 2 79 800.0000000000 ,0
145 10 2 76 800.0000000000 ,0
145 11 2 58 800.0000000000 ,0
145 12 2 67 800.0000000000 ,0
145 13 2 81 800.0000000000 ,0
145 14 2 56 800.0000000000 ,0
145 15 2 73 800.0000000000 ,0
145 16 2 93 800.0000000000 ,0
145 17 2 72 800.0000000000 ,0
145 18 2 65 800.0000000000 ,0
145 19 2 36 1600.000000000 ,0
145 20 2 90 800.0000000000 ,0
145 21 2 46 800.0000000000 ,0
145 22 2 97 800.0000000000 ,0
145 23 2 53 800.0000000000 ,0
145 24 2 84 800.0000000000 ,0
145 25 2 49 800.0000000000 ,0
145 26 2 47 800.0000000000 ,0
145 27 2 66 800.0000000000 ,0要求结果： (按mxitemno 分组，再按itemno的大小重新排序)  更新到xitemno
billid,itemno,mxitemno,materialid,quantity     ,xitemno
145 1 1 110 585.0000000000 ,1
145 2 1 111 270.0000000000 ,2
145 3 1 112 45.00000000000 ,3
145 4 2 52 800.0000000000 ,1
145 5 2 55 800.0000000000 ,2
145 6 2 63 800.0000000000 ,3
145 7 2 82 800.0000000000 ,4
145 8 2 59 800.0000000000 ,5
145 9 2 79 800.0000000000 ,6
145 10 2 76 800.0000000000 ,7
145 11 2 58 800.0000000000 ,8
145 12 2 67 800.0000000000 ,9
145 13 2 81 800.0000000000 ,10
145 14 2 56 800.0000000000 ,11
145 15 2 73 800.0000000000 ,12
145 16 2 93 800.0000000000 ,13
145 17 2 72 800.0000000000 ,14
145 18 2 65 800.0000000000 ,15
145 19 2 36 1600.000000000 ,16
145 20 2 90 800.0000000000 ,17
145 21 2 46 800.0000000000 ,18
145 22 2 97 800.0000000000 ,19
145 23 2 53 800.0000000000 ,20
145 24 2 84 800.0000000000 ,21
145 25 2 49 800.0000000000 ,22
145 26 2 47 800.0000000000 ,23
145 27 2 66 800.0000000000 ,24

Answer 1

create TABLE tb(billid int,itemno INT ,mxitemno int,materialid int,quantity numeric(15,11) ,xitemno int)
insert tb
select 145,1,1,110,585.0000000000,'0' union all
select 145,2,1,111,270.0000000000,'0' union all
select 145,3,1,112,45.00000000000,'0' union all
select 145,4,2,52,800.0000000000,'0' union all
select 145,5,2,55,800.0000000000,'0' union all
select 145,6,2,63,800.0000000000,'0' union all
select 145,7,2,82,800.0000000000,'0' union all
select 145,8,2,59,800.0000000000,'0' union all
select 145,9,2,79,800.0000000000,'0' union all
select 145,10,2,76,800.0000000000,'0' union all
select 145,11,2,58,800.0000000000,'0' union all
select 145,12,2,67,800.0000000000,'0' union all
select 145,13,2,81,800.0000000000,'0' union all
select 145,14,2,56,800.0000000000,'0' union all
select 145,15,2,73,800.0000000000,'0' union all
select 145,16,2,93,800.0000000000,'0' union all
select 145,17,2,72,800.0000000000,'0' union all
select 145,18,2,65,800.0000000000,'0' union all
select 145,19,2,36,1600.000000000,'0' union all
select 145,20,2,90,800.0000000000,'0' union all
select 145,21,2,46,800.0000000000,'0' union all
select 145,22,2,97,800.0000000000,'0' union all
select 145,23,2,53,800.0000000000,'0' union all
select 145,24,2,84,800.0000000000,'0' union all
select 145,25,2,49,800.0000000000,'0' union all
select 145,26,2,47,800.0000000000,'0' union all
select 145,27,2,66,800.0000000000,'0'UPDATE tb
SET xitemno=(SELECT COUNT(1) FROM tb t WHERE t.mxitemno =m.mxitemno  AND t.itemno<=m.itemno)
FROM tb mselect * from tb

Answer 2

create TABLE #tb(billid int,itemno INT ,mxitemno int,materialid int,quantity numeric(15,11) ,xitemno int) 
insert into #tb
select 145,1,1,110,585.0000000000,0
union all select 145,2,1,111,270.0000000000,0
union all select 145,3,1,112,45.00000000000,0
union all select 145,4,2,52,800.0000000000,0
union all select 145,5,2,55,800.0000000000,0
union all select 145,6,2,63,800.0000000000,0
union all select 145,7,2,82,800.0000000000,0
union all select 145,8,2,59,800.0000000000,0
union all select 145,9,2,79,800.0000000000,0
union all select 145,10,2,76,800.0000000000,0
union all select 145,11,2,58,800.0000000000,0
union all select 145,12,2,67,800.0000000000,0
union all select 145,13,2,81,800.0000000000,0
union all select 145,14,2,56,800.0000000000,0
union all select 145,15,2,73,800.0000000000,0
union all select 145,16,2,93,800.0000000000,0
union all select 145,17,2,72,800.0000000000,0
union all select 145,18,2,65,800.0000000000,0
union all select 145,19,2,36,1600.000000000,0
union all select 145,20,2,90,800.0000000000,0
union all select 145,21,2,46,800.0000000000,0
union all select 145,22,2,97,800.0000000000,0
union all select 145,23,2,53,800.0000000000,0
union all select 145,24,2,84,800.0000000000,0
union all select 145,25,2,49,800.0000000000,0
union all select 145,26,2,47,800.0000000000,0
union all select 145,27,2,66,800.0000000000,0update a set xitemno=b.rn
from #tb a
inner join (select *,rn=ROW_NUMBER() over(partition by mxitemno order by itemno) from #tb)b
on a.billid=b.billid and a.itemno=b.itemno

select * from #tb/*
billid itemno mxitemno materialid quantity xitemno
--------------------------------------------------------------
145 1 1 110 585.00000000000 1
145 2 1 111 270.00000000000 2
145 3 1 112 45.00000000000 3
145 4 2 52 800.00000000000 1
145 5 2 55 800.00000000000 2
145 6 2 63 800.00000000000 3
145 7 2 82 800.00000000000 4
145 8 2 59 800.00000000000 5
145 9 2 79 800.00000000000 6
145 10 2 76 800.00000000000 7
145 11 2 58 800.00000000000 8
145 12 2 67 800.00000000000 9
145 13 2 81 800.00000000000 10
145 14 2 56 800.00000000000 11
145 15 2 73 800.00000000000 12
145 16 2 93 800.00000000000 13
145 17 2 72 800.00000000000 14
145 18 2 65 800.00000000000 15
145 19 2 36 1600.00000000000 16
145 20 2 90 800.00000000000 17
145 21 2 46 800.00000000000 18
145 22 2 97 800.00000000000 19
145 23 2 53 800.00000000000 20
145 24 2 84 800.00000000000 21
145 25 2 49 800.00000000000 22
145 26 2 47 800.00000000000 23
145 27 2 66 800.00000000000 24
*/

Answer 3

谢谢你们的回复,好像在sql2000  ROW_NUMBER() over(partition by mxitemno order by itemno) 
运行不了的.

Answer 4

在sql server 2005中才支持row_number函数哈，所以sql server 2000不支持row_number函数