依字段内容自动发出序号

依字段内容自动发出序号

行事被偶然发生碰到这么的事态:比如说物料档案,编号不同而名称或条件同样之起为数不少,如下例:

人事档案 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地图