新增文章

文章标题分类内容

,row_number() over ( order by FCustName,FDate,FBillNo,FIndex ) as FIdentityId   -- 全部排序
,rank() over( partition by a11.FID  order by a12.FSeq ,a13.FSeq ) as FSort  -- 分组排序

-- 1：创建临时表

DROP TABLE #table

CREATE TABLE #table ([主管] NVARCHAR(50) ,[员工] NVARCHAR(50))
   INSERT INTO #table VALUES (N'S 骚团长',N'张大')
   INSERT INTO #table VALUES (N'S 骚团长',N'张一')
   INSERT INTO #table VALUES (N'S 骚团长',N'张二')
   INSERT INTO #table VALUES (N'S 骚团长',N'张三')
   INSERT INTO #table VALUES (N'S 骚团长',N'张四')
   INSERT INTO #table VALUES (N'H 胡泽权',N'李大炮')
   INSERT INTO #table VALUES (N'H 胡泽权',N'李一炮')
   INSERT INTO #table VALUES (N'H 胡泽权',N'李二炮')
   INSERT INTO #table VALUES (N'H 胡泽权',N'李三炮')
   INSERT INTO #table VALUES (N'H 胡泽权',N'李四炮')
   INSERT INTO #table VALUES (N'L 李海平',N'王老大')
   INSERT INTO #table VALUES (N'L 李海平',N'王一')
   INSERT INTO #table VALUES (N'L 李海平',N'王二')
   INSERT INTO #table VALUES (N'L 李海平',N'王三')
   INSERT INTO #table VALUES (N'L 李海平',N'王四')
 
--2：综合串起来分组查询编号、序号

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'S 骚团长',N'H 胡泽权',N'L 李海平') 
   ORDER BY [主管] 
   
--3：查询临时表

SELECT * FROM  #table