a b
01001 aa
01001 bb
01001 cc
01001 dd
01002 ee
01002 ff
01002 gg
01003 hh
01003 ii想得到:
a b
01001a aa
01001b bb
01001c cc
01001d dd
01002a ee
01002b ff
01002c gg
01003a hh
01003b ii
01001 aa
01001 bb
01001 cc
01001 dd
01002 ee
01002 ff
01002 gg
01003 hh
01003 ii想得到:
a b
01001a aa
01001b bb
01001c cc
01001d dd
01002a ee
01002b ff
01002c gg
01003a hh
01003b ii
if object_id('tempdb.dbo.#T') is not null drop table #T1
create table #T (a varchar(5),b varchar(2))
insert into #T
select '01001','aa' union all
select '01001','bb' union all
select '01001','cc' union all
select '01001','dd' union all
select '01002','ee' union all
select '01002','ff' union all
select '01002','gg' union all
select '01003','hh' union all
select '01003','ii'
goselect id=identity(int,1,1),* into # from #Tselect a+char(65+(select count(*) from # where a=a.a and id<a.id)) a,b
from # ago
drop table #T,#/*
a b
------ ----
01001A aa
01001B bb
01001C cc
01001D dd
01002A ee
01002B ff
01002C gg
01003A hh
01003B ii(9 row(s) affected)*/
if object_id('tempdb.dbo.#T') is not null drop table #T1
create table #T (a varchar(5),b varchar(2))
insert into #T
select '01001','aa' union all
select '01001','bb' union all
select '01001','cc' union all
select '01001','dd' union all
select '01002','ee' union all
select '01002','ff' union all
select '01002','gg' union all
select '01003','hh' union all
select '01003','ii'
goselect id=identity(int,1,1),* into # from #Tselect a+char(97+(select count(*) from # where a=a.a and id<a.id)) a,b
from # ago
drop table #T,#/*
a b
------ ----
01001a aa
01001b bb
01001c cc
01001d dd
01002a ee
01002b ff
01002c gg
01003a hh
01003b ii(9 row(s) affected)
*/
insert into tb
select '01001','aa' union all
select '01001','bb' union all
select '01001','cc' union all
select '01001','dd' union all
select '01002','ee' union all
select '01002','ff' union all
select '01002','gg' union all
select '01003','hh' union all
select '01003','ii'
goselect a = a + case when px = 1 then 'a' when px = 2 then 'b' when px = 3 then 'c' when px = 4 then 'd' end , b from
(
select * , px = (select count(1) from tb where a = t.a and b < t.b) + 1 from tb t
) mdrop table tb/*
a b
------ ----
01001a aa
01001b bb
01001c cc
01001d dd
01002a ee
01002b ff
01002c gg
01003a hh
01003b ii(所影响的行数为 9 行)
*/
(
select id=row_number() over(order by a),* from #T
)
select a+char(97+(select count(*) from CTE where a=a.a and id<a.id)) a,b
from CTE a
上面写错了..with CTE as
(
select id=row_number() over(partition by a order by a),* from #T
)
select a+char(97+id-1) a,b
from CTE a
from #T a
from #T a
/*
a b
------ ----
01001a aa
01001b bb
01001c cc
01001d dd
01002a ee
01002b ff
01002c gg
01003a hh
01003b ii(9 row(s) affected)*/
select case when (select count(*) from #T where a=a.a and b<a.b group by a) is null then a else
a + char(96 +(select count(*) from #T where a=a.a and b<a.b group by a)) end a,
b
from #T a/*(9 row(s) affected)
a b
------ ----
01001 aa
01001a bb
01001b cc
01001c dd
01002 ee
01002a ff
01002b gg
01003 hh
01003a ii(9 row(s) affected)
*/