27,579
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([A01] nvarchar(22),[A02] NVARCHAR(22))
Insert #A
select N'红色',null union all
select N'红色',null union all
select N'白色',null union all
select N'白色',null union all
select N'白色',null
Go
--测试数据结束
--1、
SELECT A01 ,
'000'
+ RTRIM(ROW_NUMBER() OVER ( PARTITION BY A01 ORDER BY GETDATE() )) AS A02
FROM #A
--2、
SELECT A01 ,
CHAR(64+
+ RTRIM(ROW_NUMBER() OVER ( PARTITION BY A01 ORDER BY GETDATE() ))) AS A02
FROM #A
drop TABLE IF EXISTS dbo.表A
create table 表A(id int IDENTITY,A01 varchar(20),A02 varchar(20))
INSERT INTO 表A(A01) VALUES('红色'),('红色'),('白色'),('白色'),('白色');
SELECT A01,format(row_number() over(partition by A01 ORDER BY (select 1)),'000') as A02 from dbo.表A
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([A01] nvarchar(22),[A02] NVARCHAR(22))
Insert #A
select N'红色',null union all
select N'红色',null union all
select N'白色',null union all
select N'白色',null union all
select N'白色',null union all
select N'白色',null union all
select N'白色',null union all
select N'白色',null union all
select N'白色',null union all
select N'白色',null union all
select N'白色',null union all
select N'白色',null union all
select N'白色',null union all
select N'白色',null
Go
--测试数据结束
--1、
SELECT A01 ,
LEFT('000',4-LEN(RTRIM(ROW_NUMBER() OVER ( PARTITION BY A01 ORDER BY GETDATE() ))))
+ RTRIM(ROW_NUMBER() OVER ( PARTITION BY A01 ORDER BY GETDATE() )) AS A02
FROM #A