22,210
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[test2] (
[顺序号] nvarchar(255) NULL ,
[编码] nvarchar(255) NULL ,
[规则] nvarchar(255) NULL
)
-- ----------------------------
-- Records of test2
-- ----------------------------
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'5', N'DX.01195.02.LQC2', N'L')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'32', N'DX.01195.02.LQC1', N'L')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'22', N'DX.01195.02.S001', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'23', N'DX.01195.02.S002', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'24', N'DX.01195.02.S003', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'25', N'DX.01195.02.S004', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'26', N'DX.01195.02.S005', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'27', N'DX.01195.02.S006', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'28', N'DX.01195.02.S007', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'29', N'DX.01195.02.S008', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'30', N'DX.01195.02.S009', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'31', N'DX.01195.02.S010', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'33', N'DX.01195.02.S011', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'34', N'DX.01195.02.S012', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'35', N'DX.01195.02.S013', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'36', N'DX.01195.02.S014', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'37', N'DX.01195.02.S015', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'38', N'DX.01195.02.S016', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'39', N'DX.01195.02.S017', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'40', N'DX.01195.02.S018', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'41', N'DX.01195.02.S019', N'S')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'1', N'DX.01195.02.A001', N'A')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'2', N'DX.01195.02.A002', N'A')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'3', N'DX.01195.02.A003', N'A')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'4', N'DX.01195.02.A005', N'A')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'17', N'DX.01195.02.P001', N'P')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'18', N'DX.01195.02.P002', N'P')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'19', N'DX.01195.02.P003', N'P')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'20', N'DX.01195.02.P004', N'P')
INSERT INTO [dbo].[test2] ([顺序号], [编码], [规则]) VALUES (N'21', N'DX.01195.02.P005', N'P')
;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER( ORDER BY CHARINDEX(规则,'S,A,P')) AS num FROM [test2] WHERE 规则 IN('S','A','P')
),cteb AS(
SELECT *,(SELECT num FROM ctea WHERE ctea.顺序号+1=dbo.test2.顺序号) AS num FROM dbo.test2 WHERE 规则='L'
),ctec AS (
SELECT * FROM ctea
UNION ALL
SELECT * FROM cteb
)SELECT 顺序号,编码,规则 FROM ctec ORDER BY num