本文主要是介绍SQL 多项排序自动加编号(分组加编号/项序/行号)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
--1：先建一张临时表如下
CREATE TABLE #mfsyscserial ( [sysserialno] varchar(50), [cserialno] varchar(50) )
INSERT INTO #mfsyscserial
VALUES
( 'YD04DJLG', '8S5CB0P2067112YD84R1428' ),
( 'YD04DJLG', '8SSN20N2521871YD84M60PK' ),
( 'YD04DJLG', '8S5C50P23900S3SZ84S0NPM' ),
( 'YD04DJLG', '8SSW10H24486T1SS84K0XMD' ),
( 'YD04DJLG', '8S5CB0P2068281YD84800DS' ),
( 'YD04DJLG', '8S5B10P2377932YD84A0611' ),
( 'YD04DJLG', '8S5D10N87520B1CH84S06PX' ),
( 'YD04DJMD', '8S5D10N87520B1CH84S06R5' ),
( 'YD04DJLG', '8S5B30P20664E1YD82T18FF' ),
( 'YD04DJMD', '8SSW10H24486T1SS84K0XM6' ),
( 'YD04DJMD', '8S5CB0P2068281YD84800E6' ),
( 'YD04DJLG', '8S5B20P23825S3SZ85B002Z' ),
( 'YD04DJLG', '67421203300050' ),
( 'YD04DJLG', '8S5CB0P2067811YD7CM2593' ),
( 'YD04DJSA', '8S5D10N87520B1CH84S06RT' ),
( 'YD04DJSA', '8S5B30P20664E1YD82T148A' ),
( 'YD04DJMD', '8S5CB0P2067112YD84R1443' ),
( 'YD04DJMD', '8S5B30P20664E1YD82T1903' ),
( 'YD04DJSA', '8S5B10P2377932YD84D1903' ),
( 'YD04DJSA', '67421203300050' ),
( 'YD04DJMD', '8SSN20N2521871YD84M60G9' ),
( 'YD04DJMD', '8S5C50P23900S3SZ84S0ND9' ),
( 'YD04DJSA', '8SSN20N2521871YD84M60QH' ),
( 'YD04DJSA', '8S5CB0P2068281YD84800V6' ),
( 'YD04DJSA', '8SSW10H24486T1SS84K0620' ),
( 'YD04DJSA', '8S5C50P23900S3SZ84S0NH8' ),
( 'YD04DJMD', '8S5B10P2377932YD84A0610' ),
( 'YD04DJMD', '67421203300050' ),
( 'YD04DJMD', '8S5B20P23825S3SZ85B0050' ),
( 'YD04DJMD', '8S5CB0P2067811YD7CM2599' ),
( 'YD04DJSA', '8S5CB0P2067112YD84R2157' ),
( 'YD04DJSA', '8S5B20P23825S3SZ85B009B' ),
( 'YD04DJSA', '8S5CB0P2067811YD7CM1904' )--2：查询刚刚新建表的信息
SELECT sysserialno,cserialno
FROM #mfsyscserial (NOLOCK) WHERE sysserialno IN ('YD04DJSA','YD04DJMD','YD04DJLG') ORDER BY sysserialno--3：所查信息自动生成序号
SELECT ROW_NUMBER () OVER (ORDER BY sysserialno) AS seqno,sysserialno,cserialno
FROM #mfsyscserial (NOLOCK) WHERE sysserialno IN ('YD04DJSA','YD04DJMD','YD04DJLG')
ORDER BY sysserialno--4：根据组别来排序生成编号项序
SELECT DENSE_RANK() OVER (ORDER BY sysserialno/*←这里放什么就是根据什么来排序生成编号项序*/) AS seqno,sysserialno,cserialno
FROM #mfsyscserial (NOLOCK) WHERE sysserialno IN ('YD04DJSA','YD04DJMD','YD04DJLG')
ORDER BY sysserialno--5：根据多个组别来排序生成编号项序
SELECT RANK() OVER(PARTITION BY sysserialno /*←这里放什么就是根据这里来编号*/ORDER BY cserialno/*←这里有多少笔数目就生成多少个编号*/) AS cserialno_seqno,sysserialno,cserialno
FROM #mfsyscserial (NOLOCK) WHERE sysserialno IN ('YD04DJSA','YD04DJMD','YD04DJLG')
ORDER BY sysserialno--6：下面是串起来的查询
SELECT
ROW_NUMBER () OVER (ORDER BY sysserialno,cserialno) AS seqno,
DENSE_RANK () OVER (ORDER BY sysserialno ) AS sysserialno_seqno ,
sysserialno,
RANK() OVER(PARTITION BY sysserialno ORDER BY cserialno ) AS cserialno_seqno,cserialno
FROM #mfsyscserial (NOLOCK) WHERE sysserialno IN ('YD04DJSA','YD04DJMD','YD04DJLG')
ORDER BY sysserialno/******************************************** 下面来一个比较形象的表结构作为范例*******************************************/
-- 1.：创建临时表 CREATE TABLE #table ([线长] NVARCHAR(50) ,[员工] NVARCHAR(50))INSERT INTO #table VALUES (N'骚团长',N'张大')
INSERT INTO #table VALUES (N'骚团长',N'张一')
INSERT INTO #table VALUES (N'骚团长',N'张二')
INSERT INTO #table VALUES (N'骚团长',N'张三')
INSERT INTO #table VALUES (N'骚团长',N'张四')
INSERT INTO #table VALUES (N'胡泽权',N'李大炮')
INSERT INTO #table VALUES (N'胡泽权',N'李一炮')
INSERT INTO #table VALUES (N'胡泽权',N'李二炮')
INSERT INTO #table VALUES (N'胡泽权',N'李三炮')
INSERT INTO #table VALUES (N'胡泽权',N'李四炮')
INSERT INTO #table VALUES (N'李海平',N'王老大')
INSERT INTO #table VALUES (N'李海平',N'王一')
INSERT INTO #table VALUES (N'李海平',N'王二')
INSERT INTO #table VALUES (N'李海平',N'王三')
INSERT INTO #table VALUES (N'李海平',N'王四')--2：查询临时表
SELECT * FROM #table--3：综合串起来分组查询编号、序号
SELECT
ROW_NUMBER () OVER (ORDER BY [线长] ,[员工]) AS 总编号排序,
DENSE_RANK () OVER (ORDER BY [线长] ) AS 线长_排序 ,[线长] ,
RANK() OVER(PARTITION BY [线长] ORDER BY [员工] ) AS 员工归属线长_排序,[员工]
FROM #table (NOLOCK) WHERE [线长] IN (N'骚团长',N'胡泽权',N'李海平')
ORDER BY [线长] 这篇关于SQL 多项排序自动加编号(分组加编号/项序/行号)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
