标签归档SQL Server

依字段内容自动发出序号

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

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

依字段内容自动发出序号

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

澳门新葡亰网址 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

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

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

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

网站地图xml地图