行事被偶然发生碰到这么的事态:比如说物料档案,编号不同而名称或条件同样之起为数不少,如下例:
一经用针对达标图一律标准进行编号,比如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]
履人事档案之结果如下:
办事负有时有遇上这样的状:比如说物料档案,编号不同但名称或者标准同样的生好多,如下例:
如果用针对上图一律标准进行编号,比如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]
履之澳门新葡亰网址结果如下:
办事负偶尔生遇上这样的气象:比如说物料档案,编号不同但名称或标准同样的有无数,如下例:
假使用对直达图一律标准进行编号,比如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]
履的档案馆结果如下: