--RANK 排序(每個分組進行單獨排名,相同名次的會占位,比如兩個第二名,就沒有第三名了,直接到第四名)select FenXiaoId,PRice,RANK() OVER( --使用RANK() 進行排名PARTITION BY FenxiaoID --使用fenxiaoid進行分組ORDER BY Price Desc    --使用Price進行排序) as PaiMing from PaymentOrder--DENSE_RANK排序(名次不占位,其他和RANK一樣)select FenXiaoId,Price,DENSE_RANK() OVER( --使用DENSE_RANK() 進行排名PARTITION BY FenxiaoID --使用fenxiaoid進行分組ORDER BY Price Desc    --使用Price進行排序) as PaiMing from PaymentOrder--NTILE()函數進行分組,指定范圍select FenXiaoId,Price,NTILE(5) OVER( --使用NTILE()只能排序直到5 ORDER BY Price Desc    --使用Price進行排序) as PaiMing from PaymentOrder----NTILE()使用場景select FenXiaoId,Price, CASE NTILE(4) OVER (ORDER BY Price DESC)           WHEN 1 THEN '25%'          WHEN 2 THEN '25%-50%'          WHEN 3 THEN '50%-75%'          WHEN 4 THEN '75%-100%' END AS Level          from PaymentOrder--ROW_NUMBER() 函數  分頁示例----創建分頁存儲過程 create proc Pro_UserInfo_GetPageUserInfos @PageSize int, @pageIndex int, @TotalCount int  output as select * from (  select *,ROW_NUMBER() over(Order by UserId asc) as RowNum from UserInfo ) as T where T.RowNum between ((@PageIndex-1)*@PageSize+1)  and (@PageSize*@PageIndex) select @TotalCount=count(1) from UserInfo;--捕獲異常BEGIN TRYSELECT Price/0 FROM PaymentOrderEND TRYBEGIN CATCHSELECT ERROR_LINE(),ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_MESSAGE()END CATCH

--使用CROSS APPLY 可以連接函數 select p.usecounts,p.cacheobjtype,p.objtype,s.text fromsys.dm_exec_cached_plans pcross apply sys.dm_exec_sql_text(plan_handle) s;

--使用PIVOT 進行行轉列 [值],行記錄里有多少值,方括號里就要寫多少值,注意下select FenXiaoId,[1] as "一月份",[2] as "二月份",[3] as "三月份",[4] as "四月份",[5] as "五月份",[6] as "六月份",[7] as "七月份",[8] as "八月份",[9] as "九月份",[10] as "十月份",[11] as "十一月份",[12] as "十二月份" from(select c.FenXiaoId,sum(c.Price) sumPrice,MONTH(c.ConfirmDate) MonthNumber from PaymentOrder c where c.ConfirmDate>'2016-1-1' group by MONTH(c.ConfirmDate),c.FenXiaoId) aa PIVOT --進行轉列操作 ( sum(sumPrice) for MonthNumber in  ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) as MONTHS;--使用UNPIVOT 進行列轉行select TableId,ziduan,jiage from (select top 3 a.TableId,a.PayPrice,a.Price,a.YouHui from PaymentOrder a) aa UNPIVOT (jiagefor ziduan in([price],[PayPrice],[youhui])) AS UNPVT--將數據刪除,然后將刪除的數據輸出,類似的還有 inserted updateddelete from PaymentOrderoutput deleted.*where TableId=1 --使用變量的top 查詢declare @p intset @p=100select top(@p) * from PaymentOrder;--使用TABLESAMPLE 示例數據select * from PaymentOrder TABLESAMPLE(10 PERCENT);--獲取10%的數據select * from PaymentOrder TABLESAMPLE(200 rows);--獲取大約200行的數據--使用累加運算符declare @i int=2set @i*=2 --累加運算PRINT @i;--一次插入多條數據create table #t (c1 int,c2 nvarchar(10));insert #t values (1,'a'),(2,'b') select c1,c2,c3 from #t inner join ( values(1,'aa'),(3,'cc') --內連接多條數據) t (c3,c4) on #t.c1=t.c3

--GROUPING SETS 的使用,不同字段分別分組,然后合并(不同于不同字段同時分組的情況)select FenXiaoId,XingShiId,COUNT(1) from PaymentOrder GROUP BY GROUPING SETS(FenXiaoId,XingShiId)