在线求指教..这种情况要怎么来批量处理？？
class01 class02 class03
101 10101 10101001
101 10101 10101002
101 10101 10101003
102 10201 10201001
102 10201 10201002
103 10301 10301001
103 10301 10301002
103 10301 10301003
怎么根据class01和class02来得到class03的值？？？
[解决办法]
;with cte(class01,class02) as
(
select 101,10101
union all select 101,10101
union all select 101,10101
union all select 102,10201
union all select 102,10201
union all select 103,10301
union all select 103,10301
union all select 103,10301
)
select class01,class02
,class03=cast(class02 as varchar)+RIGHT('00'+cast(rn as varchar),3)
from
(
select *,rn=ROW_NUMBER() over(partition by class01,class02 order by getdate()) from cte
)t
/*
class01class02class03
1011010110101001
1011010110101002
1011010110101003
1021020110201001
1021020110201002
1031030110301001
1031030110301002
1031030110301003
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-18 17:51:47
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([class01] int,[class02] int,[class03] int)
insert [huang]
select 101,10101,10101001 union all
select 101,10101,10101002 union all
select 101,10101,10101003 union all
select 102,10201,10201001 union all
select 102,10201,10201002 union all
select 103,10301,10301001 union all
select 103,10301,10301002 union all
select 103,10301,10301003
--------------开始查询--------------------------
select class01 ,class02 ,CONVERT(VARCHAR(10),class02)+RIGHT('000'+CONVERT(VARCHAR(3),ROW_NUMBER() over(PARTITION BY [class01] ORDER BY [class01],[class02])),3)[class03]
from [huang]
----------------结果----------------------------
/*
class01 class02 class03
----------- ----------- ----------------
101 10101 10101001
101 10101 10101002
101 10101 10101003
102 10201 10201001
102 10201 10201002
103 10301 10301001
103 10301 10301002
103 10301 10301003
*/
