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 TRY
SELECT Price/0 FROM PaymentOrder
END TRY
BEGIN CATCH
SELECT ERROR_LINE(),ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_MESSAGE()
END CATCH

--使用CROSS APPLY 可以连接函数
select p.usecounts,p.cacheobjtype,p.objtype,s.text from
sys.dm_exec_cached_plans p
cross 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 (
jiage
for ziduan in
([price],[PayPrice],[youhui])
) AS UNPVT
--将数据删除,然后将删除的数据输出,类似的还有 inserted updated
delete from PaymentOrder
output deleted.*
where TableId=1
--使用变量的top 查询
declare @p int
set @p=100
select top(@p) * from PaymentOrder;
--使用TABLESAMPLE 示例数据
select * from PaymentOrder TABLESAMPLE(10 PERCENT);--获取10%的数据
select * from PaymentOrder TABLESAMPLE(200 rows);--获取大约200行的数据
--使用累加运算符
declare @i int=2
set @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)