SQL语句备忘

--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)