SQL2000分组后再按大小排序更新
SQL2000分组后再按大小排序更新
(按mxitemno 分组，再按itemno的大小重新排序) 更新到xitemno
表结构如下:
billid,itemno,mxitemno,materialid,quantity ,xitemno
14511110585.0000000000 ,0
14521111270.0000000000 ,0
1453111245.00000000000 ,0
1454252800.0000000000 ,0
1455255800.0000000000 ,0
1456263800.0000000000 ,0
1457282800.0000000000 ,0
1458259800.0000000000 ,0
1459279800.0000000000 ,0
14510276800.0000000000 ,0
14511258800.0000000000 ,0
14512267800.0000000000 ,0
14513281800.0000000000 ,0
14514256800.0000000000 ,0
14515273800.0000000000 ,0
14516293800.0000000000 ,0
14517272800.0000000000 ,0
14518265800.0000000000 ,0
145192361600.000000000 ,0
14520290800.0000000000 ,0
14521246800.0000000000 ,0
14522297800.0000000000 ,0
14523253800.0000000000 ,0
14524284800.0000000000 ,0
14525249800.0000000000 ,0
14526247800.0000000000 ,0
14527266800.0000000000 ,0
要求结果： (按mxitemno 分组，再按itemno的大小重新排序) 更新到xitemno
billid,itemno,mxitemno,materialid,quantity ,xitemno
14511110585.0000000000 ,1
14521111270.0000000000 ,2
1453111245.00000000000 ,3
1454252800.0000000000 ,1
1455255800.0000000000 ,2
1456263800.0000000000 ,3
1457282800.0000000000 ,4
1458259800.0000000000 ,5
1459279800.0000000000 ,6
14510276800.0000000000 ,7
14511258800.0000000000 ,8
14512267800.0000000000 ,9
14513281800.0000000000 ,10
14514256800.0000000000 ,11
14515273800.0000000000 ,12
14516293800.0000000000 ,13
14517272800.0000000000 ,14
14518265800.0000000000 ,15
145192361600.000000000 ,16
14520290800.0000000000 ,17
14521246800.0000000000 ,18
14522297800.0000000000 ,19
14523253800.0000000000 ,20
14524284800.0000000000 ,21
14525249800.0000000000 ,22
14526247800.0000000000 ,23
14527266800.0000000000 ,24
[解决办法]
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 m
select * from tb
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,0
update 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
/*
billiditemnomxitemnomaterialidquantityxitemno
--------------------------------------------------------------
14511110585.000000000001
14521111270.000000000002
1453111245.000000000003
1454252800.000000000001
1455255800.000000000002
1456263800.000000000003
1457282800.000000000004
1458259800.000000000005
1459279800.000000000006
14510276800.000000000007
14511258800.000000000008
14512267800.000000000009
14513281800.0000000000010
14514256800.0000000000011
14515273800.0000000000012
14516293800.0000000000013
14517272800.0000000000014
14518265800.0000000000015
145192361600.0000000000016
14520290800.0000000000017
14521246800.0000000000018
14522297800.0000000000019
14523253800.0000000000020
14524284800.0000000000021
14525249800.0000000000022
14526247800.0000000000023
14527266800.0000000000024
*/
