SQL Server 总结复习(一)

1. tvp， 表变量，临时表，cte 的区别
tvp和临时表都是可以索引的，总是存在tempdb中，会增加系统数据库开销，而表变量和cte只有在内存溢出时才会被写入tempdb中。对于数据量大，并且反复使用，反复进行查询关联的，建议使用临时表或tvp，数据量小，使用表变量或cte比较合适
2. sql_variant 万能类型
可以存放所有数据类型，相当于c#中的object数据类型
3. datetime, datetime2, datetimeoffset
datetime 时间有效期较小，在1753-1-1 之前就不能使用了，精度为毫秒级别，而datetime2 数据范围相当于c#中的datetime ，精度达到了秒后面小数点后7位，datetimeoffset则是考虑是时区的日期类型
4. merge的用法
语法很简单就不说了，主要是处理两张表某些字段对比后的操作，需注意 when not matched (by target) 与 when not matched by source的区别，前者是是针对对比后目标表不存在的记录，可以选择insert操作，而后者则是针对对比后目标表多出来的记录，可以选择delete或update操作
5. rowversion 类型
代替以前的timestamp，时间戳，8字节二进制值，常用来进行解决并发操作的问题
6. sysdatetime()
返回datetime2类型，精度比datetime高
7. with cube , with rollup , grouping sets 运算符
都可与group by 后连用，with cube 表示汇总所有级别的组合，with rollup 则是按级别汇总，从下面的代码可以详细看出区别。注意，汇总行，null可以看成所有值
而grouping sets运算符，则仅返回每个分组*汇总行，在查询汇总行中 可使用grouping(字段名) = 1来判断，该运算符可和rollup, cube连用，表示按照grouping by sets和按照rollup/cube处理的结果集union all
示例代码如下：

with cube, with rollup
--示例代码
declare @t table(goodsname varchar(max) ,sku1name varchar(max) , sku2name varchar(max), qty int)
insert @t select '凡客tx','红色','s',1
insert @t select '凡客tx','黑色','s',2
insert @t select '凡客tx','白色','l',3
insert @t select '京东村山','白色','l',4
insert @t select '京东村山','红色','s',5
insert @t select '京东村山','黑色','l',6
insert @t select '亚马逊拖鞋','白色','l',7
insert @t select '亚马逊拖鞋','红色','s',8
select * from @t
select goodsname,sku1name,sku2name,sum(qty) sumqty
from @t
group by goodsname,sku1name,sku2name with rollup
order by goodsname,sku1name,sku2name
select goodsname,sku1name,sku2name,sum(qty) sumqty
from @t
group by goodsname,sku1name,sku2name with cube
order by goodsname,sku1name,sku2name
-----------------------
declare @t table(goodsname varchar(max) ,sku1name varchar(max) , sku2name varchar(max), qty int)
insert @t select '凡客tx','红色','s',1
insert @t select '凡客tx','黑色','s',2
insert @t select '凡客tx','白色','l',3
insert @t select '京东村山','白色','l',4
insert @t select '京东村山','红色','s',5
insert @t select '京东村山','黑色','l',6
insert @t select '亚马逊拖鞋','白色','l',7
insert @t select '亚马逊拖鞋','红色','s',8
--grouping sets 运算符
select goodsname,sku1name,sku2name, sum(qty) from @t group by grouping sets(goodsname,sku1name,sku2name)
select goodsname, sku1name, sku2name ,sum(qty) from @t
group by grouping sets(goodsname), rollup(sku1name,sku2name)
order by goodsname,sku1name,sku2name
select goodsname, sku1name, sku2name ,sum(qty) from @t
group by rollup(goodsname,sku1name,sku2name)
order by goodsname,sku1name,sku2name
select case when grouping(goodsname) = 1 then '[all]' else goodsname end goodsname,
case when grouping(sku1name) = 1 then '[all]' else sku1name end sku1name,
case when grouping(sku2name) = 1 then '[all]' else sku2name end sku2name ,sum(qty) from @t
group by grouping sets(goodsname), rollup(sku1name,sku2name)
order by goodsname,sku1name,sku2name

8. 一些快捷的语法 例如 declare @id int = 0
虽然有时很快捷，但dba不建议这样使用，declare @id = select top 1 id from 表名，建议声明和查表赋值分开
9. 公用表达式 cte
特点：可嵌套使用，代替联接表中的子查询，结构层次更加清晰，也可用来递归查询，另外通过巧妙的常量列控制递归层次
示例代码如下：

--公用表达式cte common table expression
--用cte实现递归算法
create table employeetree(
employee int primary key,
employeename nvarchar(50),
reportsto int
)
insert into employeetree values(1,'richard',null)
insert into employeetree values(2,'stephen',1)
insert into employeetree values(3,'clemens',2)
insert into employeetree values(4,'malek',2)
insert into employeetree values(5,'goksin',4)
insert into employeetree values(6,'kimberly',1)
insert into employeetree values(7,'ramesh',5)
----------------------
--确定哪些员工向stephen报告的递归查询
with employeetemp as
(
select employee, employeename, reportsto from employeetree where employee = 2
union all
select a.employee, a.employeename, a.reportsto from employeetree as a
inner join employeetemp as b on a.reportsto = b.employee
)
select * from employeetemp where employee <> 2 --option(maxrecursion 2)
--不报错设置级联关联递归
with employeetemp as
(
select employee, employeename, reportsto,0 as sublevel from employeetree where employee = 2
union all
select a.employee, a.employeename, a.reportsto,sublevel+1 from employeetree as a
inner join employeetemp as b on a.reportsto = b.employee
)
select * from employeetemp where employee <> 2 and sublevel <=2 --option(maxrecursion 2)

10. pivot 与 unpivot
前者用在行转列，注意：必须用聚合函数与pivot一起使用，计算聚会时将不考虑出现在值列中的任何空值；一般情况下，可以用列上的子查询来替换pivot语句，但是这样做效率不高
后者用在列转行，注意：如果某些列中有null值，将会被过滤掉，不产生新行；语法上for前指定的新列，对应原表指定列名中的值，for后指定的新列对应原表指定列名中的标题的值
两者都有的共性：语法上最后必须要有别名；in里面指定的列类型必须是一致的。
示例代码如下：

pivot与unpivot
--关于pivot的操作
create table #test
(
name varchar(max),
score int
)
insert into #test values ('张三','97')
insert into #test values ('李四','28')
insert into #test values ('王五','33')
insert into #test values ('神人','78')
--name score
--张三 97
--李四 28
--王五 33
--神人 78
--行转列
select --'成绩单' as scorename ,
[张三], [李四], [王五]
from #test
pivot (avg(score) for name in ([张三], [李四], [王五])) b
-----------------------------------------
create table vendoremployee(
vendorid int,
emp1order int,
emp2order int,
emp3order int,
emp4order int,
emp5order int,
)
go
insert into vendoremployee values(1,4,3,5,4,4)
insert into vendoremployee values(2,4,1,5,5,5)
insert into vendoremployee values(3,4,3,5,4,4)
insert into vendoremployee values(4,4,2,5,4,4)
insert into vendoremployee values(5,5,1,5,5,5)
select * from vendoremployee
----------------
--列转行
select * from (
select vendorid,[emp1order],[emp2order],[emp3order],[emp4order],[emp5order] from vendoremployee) as unpiv
unpivot (orders for elyid in ([emp1order],[emp2order],[emp3order],[emp4order],[emp5order])) as child
order by elyid
select * from vendoremployee
unpivot (orders for elyid in ([emp1order],[emp2order],[emp3order],[emp4order],[emp5order])) as child
order by elyid
select * from vendoremployee unpivot ( orders for [操作员名字] in ([emp1order],[emp2order],[emp3order],[emp4order],[emp5order]))