依字段内容自动发出序号

依字段内容自动发出序号

办事负有时有遇上这样的状:比如说物料档案,编号不同但名称或者标准同样的生好多,如下例:

澳门新葡亰网址 1

如果用针对上图一律标准进行编号,比如A1、A2、B1、B2、B3……,该怎么处理吧?

否便利演示,先成立测试环境:

CREATE TABLE [dbo].[1TEST](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SPC] [varchar](50) NULL,
 CONSTRAINT [PK_1TEST] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO [1TEST] (SPC)
SELECT 'A'
UNION ALL
SELECT 'A'
UNION ALL
SELECT 'B'
UNION ALL
SELECT 'B'
UNION ALL
SELECT 'B'
UNION ALL
SELECT 'C'
UNION ALL
SELECT 'C'
UNION ALL
SELECT 'C'
UNION ALL
SELECT 'C'

GO

处理的方式十分粗略,使用系统自动的函数ROW_NUMBER(),可以高速达成目的。

SELECT SPC,ROW_NUMBER() OVER (PARTITION BY SPC ORDER BY SPC) XH FROM [1TEST]

履之澳门新葡亰网址结果如下:

澳门新葡亰网址 2

admin

网站地图xml地图