档案馆依字段内容自动出序号

档案馆依字段内容自动出序号

办事负偶尔生遇上这样的气象:比如说物料档案,编号不同但名称或标准同样的有无数,如下例:

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