declare @a table(a varchar(100),b smalldatetime) insert @a select 'a', '06-01-01' union all select 'a' ,'06-02-01' union all select 'b' ,'06-07-01' union all select 'b' ,'06-07-02'select a+case when cou=0 then '' else ltrim(cou) end a,convert(varchar(8),b,10) from (select *,cou=(select count(1) from @a where a=a.a and b<a.b) from @a a) aa
declare @a table(名称 varchar(4),时间 varchar(10)) insert into @a select 'a','06-01-01' union all select 'a','06-02-01' union all select 'b','06-07-01' union all select 'b','06-07-02' select 名称 +ltrim(row_number() over(partition by 名称 order by 时间)) 名称,时间 from @a /* 名称 时间 ---------------------------- ---------- a1 06-01-01 a2 06-02-01 b1 06-07-01 b2 06-07-02 */
sorry:declare @a table(名称 varchar(4),时间 varchar(10)) insert into @a select 'a','06-01-01' union all select 'a','06-02-01' union all select 'b','06-07-01' union all select 'b','06-07-01' union all select 'b','06-07-02' select 名称 +case ltrim((row_number() over(partition by 名称 order by 时间))-1) when '0' then '' else ltrim((row_number() over(partition by 名称 order by 时间))-1) end 名称,时间 from @a /* 结果 名称 时间 ---------------------------- ---------- a 06-01-01 a1 06-02-01 b 06-07-01 b1 06-07-01 b2 06-07-02 */
insert @a select 'a', '06-01-01'
union all select 'a' ,'06-02-01'
union all select 'b' ,'06-07-01'
union all select 'b' ,'06-07-02'select a+case when cou=0 then '' else ltrim(cou) end a,convert(varchar(8),b,10) from
(select *,cou=(select count(1) from @a where a=a.a and b<a.b) from @a a)
aa
insert into @a
select 'a','06-01-01' union all
select 'a','06-02-01' union all
select 'b','06-07-01' union all
select 'b','06-07-02'
select 名称 +ltrim(row_number() over(partition by 名称 order by 时间)) 名称,时间 from @a
/*
名称 时间
---------------------------- ----------
a1 06-01-01
a2 06-02-01
b1 06-07-01
b2 06-07-02
*/
insert into @a
select 'a','06-01-01' union all
select 'a','06-02-01' union all
select 'b','06-07-01' union all
select 'b','06-07-01' union all
select 'b','06-07-02'
select 名称 +case ltrim((row_number() over(partition by 名称 order by 时间))-1) when '0' then '' else ltrim((row_number() over(partition by 名称 order by 时间))-1) end 名称,时间 from @a
/*
结果
名称 时间
---------------------------- ----------
a 06-01-01
a1 06-02-01
b 06-07-01
b1 06-07-01
b2 06-07-02
*/