Sqlserver临时表的应用（groupby后加入序列号）

 SELECT * INTO #TMP FROM ( select   ROW_NUMBER() OVER (ORDER BY jibiename) AS rowno, jibiename from TuanTiTongji group by jibieName) T  --创建级别名称表
 --select * from #TMP;
 DECLARE @TempId INT;--临时 Id
 DECLARE @Count INT;--临时 Id
 DECLARE @JibieNameTemp nvarchar(20);--临时 Id
 set @TempId = 1;
 select @Count=COUNT(*) from #TMP ; 
 while @TempId< @Count --循环临时
 begin  
      select @JibieNameTemp=jibiename from #TMP where rowno = @TempId ;
	 -- select @JibieNameTemp;
	  IF OBJECT_ID('tempdb..##TempThing') IS NOT NULL-- 此处必须有tempdb..  含义是如果不存在就创建，如果存在就插入数据
	  begin 
	    --select '不存在';
	    insert into ##TempThing select * from (select *, ROW_NUMBER() OVER (ORDER BY TMingciTongji) as Paixu from   TuantiTongji where jibieName = @JibieNameTemp) R;---
	  end 
	  else
	  begin
	     --select '存在';创建临时数据表
	     SELECT * INTO ##TempThing  from (select *,ROW_NUMBER() OVER (ORDER BY TMingciTongji) as Paixu  from  TuantiTongji where jibieName = @JibieNameTemp ) F; 
	  end 
	  set  @TempId= @TempId+1;
 end
 select * from ##TempThing
 drop table #TMP;
 drop table ##TempThing;--需要及时删除创建的临时表

加入与排序