新增文章

文章标题分类内容

--1：先建一张临时表如下
 
   DROP TABLE #table2
   
   CREATE TABLE #table2 ( [sysserialno] varchar(50), [cserialno] varchar(50) )
   
   INSERT INTO #table2
   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 #table2 (NOLOCK) WHERE sysserialno IN ('YD04DJSA','YD04DJMD','YD04DJLG') ORDER BY sysserialno
 
--3：所查信息自动生成序号

SELECT ROW_NUMBER () OVER (ORDER BY sysserialno) AS seqno,sysserialno,cserialno
   FROM #table2 (NOLOCK) WHERE sysserialno IN ('YD04DJSA','YD04DJMD','YD04DJLG') 
   ORDER BY sysserialno
 
--4：根据组别来排序生成编号项序

SELECT DENSE_RANK()  OVER (ORDER BY sysserialno/*←这里放什么就是根据什么来排序生成编号项序*/) AS seqno,sysserialno,cserialno
   FROM #table2 (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 #table2 (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 #table2 (NOLOCK) WHERE sysserialno IN ('YD04DJSA','YD04DJMD','YD04DJLG') 
   ORDER BY sysserialno