分组后其他列顺序编号

Question

本帖最后由 ping_net 于 2010-07-23 19:12:54 编辑

Answer 1

[sql server] update中变量的赋值用法 实现分组更新序号 
http://blog.csdn.net/xys_777/archive/2010/07/21/5753206.aspx

Answer 2

select olditem,item,itemname,lsh=right(ltrim(row_number() over(partition by item,itemname order by item,itemname)),4)
from tb

Answer 3

谢谢各位。
在同事的帮助下，同时参考两位的回复，得出了2个解决方案，一并贴出来，与大家一起分享：
法一：
SELECT olditem,
       item,
       itemname,
       lsh = RIGHT('0000'+
           LTRIM(
               ROW_NUMBER() OVER(PARTITION BY item, itemname ORDER BY item, itemname)
           ),
           4
       )
FROM   group_iden

Answer 4

法二：
SELECT a.olditem,
       a.item,
       a.itemname,
       (
           SELECT RIGHT('0000' + LTRIM(RTRIM(STR(COUNT(*)))), 4)
           FROM   group_iden b
           WHERE  b.item = a.item
                  AND b.itemname = a.itemname
                  AND b.olditem <= a.olditem
       ) AS lsh
FROM   group_iden a

Answer 5

select olditem,item,itemname,lsh=right(POWER(10,4) + row_number() over(partition by item,itemname order by olditem),4)
from group_iden
order by olditem,itemname desc

Answer 6

格式化数字，前面补0的时候可以用RIGHT(10000+num,4)

Answer 7

参考了你的更新算法发现存在问题，如果分组列是无序的就存在问题。
一本题为例：if exists (select 1 from sys.objects where object_id = object_id(N'group_iden') and type = N'U')
drop table group_iden
go
create table group_iden
(
olditem varchar(32),
item varchar(32),
itemname varchar(32),
lsh varchar(32)
)
--插入测试数据：
insert into group_iden(olditem,item,itemname) values ('e001','n001','jack');
insert into group_iden(olditem,item,itemname) values ('e002','n001','jack');
insert into group_iden(olditem,item,itemname) values ('e003','n002','jack');
insert into group_iden(olditem,item,itemname) values ('e004','n001','jack');
insert into group_iden(olditem,item,itemname) values ('e004','n001','andy');
go
--原始算法
declare @i int,@item varchar(32),@itemname varchar(32)
set @i=1
update group_iden
set lsh=right(10000+@i,4),
@i=case when (@item = item and @itemname=itemname) then @i+1 else 1 end,
@item = item,@itemname=itemname
goselect * from group_iden order by lsh
/*
olditem                          item                             itemname                         lsh
-------------------------------- -------------------------------- -------------------------------- --------------------
e001                             n001                             jack                             0001
e003                             n002                             jack                             0001
e004                             n001                             jack                             0001
e004                             n001                             andy                             0001
e002                             n001                             jack                             0002
*/
--改进后
declare @i int,@num int
set @i=1
;with t as(
select dense_rank() over(order by item,itemname ) as num,* from group_iden )
update t
set lsh=right(10000+@i,4),
@i=case when @num = num then @i+1 else 1 end,
@num=num
go
select * from group_iden order by lsh 
/*
olditem                          item                             itemname                         lsh
-------------------------------- -------------------------------- -------------------------------- --------------------
e003                             n002                             jack                             0001
e004                             n001                             jack                             0001
e004                             n001                             andy                             0001
e001                             n001                             jack                             0002
e002                             n001                             jack                             0003(5 row(s) affected)
*/