时间：2021-07-01 10:21:17 帮助过：10人阅读
这里使用了MS SQL2008的VALUES子句格式语法，这时2008版本的新特性。如果你使用的是2005及以下版本，你需要多个INSERT语句。最后的执行结果如下图所示：
（2）需求说明
假设我们要生成一个报表，包含每个员工和客户组合之间的总订货量。用以下简单的分组查询可以解决这个问题：
select empid,custid,SUM(qty) as sumqty from dbo.Orders group by empid,custid;
该查询的执行结果如下：
不过，假设现在要求要按下表所示的的格式来生成输出结果：
这时，我们就需要进行透视转换了！
（3）使用标准SQL进行透视转换
Step1.分组：GROUP BY empid;
Step2.扩展：CASE WHEN custid=‘A‘ THEN qty END;
Step3.聚合：SUM(CASE WHEN custid=‘A‘ THEN qty END);
--1.1标准SQL透视转换 select empid, SUM(case when custid=‘A‘ then qty end) as A, SUM(case when custid=‘B‘ then qty end) as B, SUM(case when custid=‘C‘ then qty end) as C, SUM(case when custid=‘D‘ then qty end) as D from dbo.Orders group by empid;
执行结果如下图所示：
（4）使用T-SQL PIVOT运算符进行透视转换
自SQL Server 2005开始引入了一个T-SQL独有的表运算符-PIVOT，它可以对某个源表或表表达式进行操作、透视数据，再返回一个结果表。
PIVOT运算符同样涉及前面介绍的三个逻辑处理阶段（分组、扩展和聚合）以及同样的透视转换元素，但使用的是不同的、SQL Server原生的语法。
下面是使用PIVOT运算符实现上面一样的效果：
select empid,A,B,C,D from (select empid,custid,qty from dbo.Orders) as D pivot (sum(qty) for custid in (A,B,C,D)) as P;
其中，PIVOT运算符的圆括号内要指定聚合函数（本例中SUM）、聚合元素（本例中的qty）、扩展元素（custid）以及目标列名称的列表（本例中的A、B、C、D）。在PIVOT运算符的圆括号后面，可以为结果表制定一个别名。
Tip：使用PIVOT运算符一般不直接把它应用到源表（本例中的Orders表），而是将其应用到一个表表达式（该表表达式只包含透视转换需要的3种元素，不包含其他属性。）此外，不需要为它显式地指定分组元素，也就不需要再查询中使用GROUP BY子句。
所谓逆透视（Unpivoting）转换是一种把数据从列的状态旋转为行的状态的技术，它将来自单个记录中多个列的值扩展为单个列中具有相同值得多个记录。换句话说，将透视表中的每个源行潜在地转换成多个行，每行代表源透视表的一个指定的列值。
还是通过一个栗子来理解：
（1）首先还是准备一下数据：
USE tempdb; IF OBJECT_ID(‘dbo.EmpCustOrders‘, ‘U‘) IS NOT NULL DROP TABLE dbo.EmpCustOrders; SELECT empid, A, B, C, D INTO dbo.EmpCustOrders FROM (SELECT empid, custid, qty FROM dbo.Orders) AS D PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P; SELECT * FROM dbo.EmpCustOrders;View Code
下面是对这个表EmpCustOrders的查询结果：
（2）需求说明
要求执行你透视转换，为每个员工和客户组合返回一行记录，其中包含这一组合的订货量。期望的输出结果如下图所示：
（3）标准SQL进行逆透视转换
Step1.生成副本：CROSS JOIN 交叉联接生成多个副本
Step2.提取元素：通过CASE语句生成qty数据列
Step3.删除不相关的交叉：过滤掉NULL值
select * from (select empid, custid, case custid when ‘A‘ then A when ‘B‘ then B when ‘C‘ then C when ‘D‘ then D end as qty from dbo.EmpCustOrders cross join (VALUES(‘A‘),(‘B‘),(‘C‘),(‘D‘)) as Custs(custid)) as D where qty is not null;
执行结果如下图所示：
（4）T-SQL UNPIVOT运算符进行逆透视转换
和PIVOT类似，在SQL Server 2005引入了一个UNPIVOT运算符，它的作用刚好和PIVOT运算符相反，即我们可以拿来做逆透视转换工作。UNPIVOT同样会经历我们上面提到的三个阶段。继续上面的栗子，我们使用UNPIVOT来进行逆透视转换：
select empid, custid, qty from dbo.EmpCustOrders unpivot (qty for custid in (A,B,C,D)) as U;
其中，UNPIVOT运算符后边的括号内包括：用于保存源表列值的目标列明（这里是qty），用于保存源表列名的目标列名（这里是custid），以及源表列名列表（A、B、C、D）。同样，在UNPIVOT括号后面也可以跟一个别名。
Tip：对经过透视转换所得的表再进行逆透视转换，并不能得到原来的表。因为你透视转换只是把经过透视转换的值再旋转岛另一种新的格式。
首先了解一下分组集：分组集就是分组（GROUP BY子句）使用的一组属性（或列名）。在传统SQL中，一个聚合查询只能定义一个分组集。为了灵活而有效地处理分组集，SQL Server 2008引入了几个重要的新功能（他们都是GROUP BY的从属子句，需要依赖于GROUP BY子句）：
（1）GROUPING SETS从属子句
使用该子句，可以方便地在同一个查询中定义多个分组集。例如下面，我们定义了4个分组集：(empid,custid),(empid),(custid)和()：
--3.1GROUPING SETS从属子句 select empid,custid,SUM(qty) as sumqty from dbo.Orders group by GROUPING SETS ( (empid,custid), (empid), (custid), () );
这个查询相当于执行了四个group by查询的并集。
（2）CUBE从属子句
CUBE子句为定义多个分组集提供了一种更简略的方法，可以把CUBE子句看作是用于生成分组的幂集。例如：CUBE(a,b,c)等价于GROUPING SETS[(a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),()]。下面我们用CUBE来实现上面的例子：
--3.2CUEE从属子句 select empid,custid,SUM(qty) as sumqty from dbo.Orders group by cube(empid,custid);
（3）ROLLUP从属子句
ROLLUP子句也是一种简略的方法，只不过它与CUBE不同，它强调输入成员之间存在一定的层次关系，从而生成让这种层次关系有意义的所有分组集。例如：CUBE(a,b,c)会生成8个可能的分组集，而ROLLUP则认为3个输入成员存在a>b>c的层次关系，所以只会生成4个分组集：(a,b,c),(a,b),(a),()。
下面我们假设想要按时间层次关系：订单年份>订单月份>订单日，以这样的关系来定义所有分组集，并未每个分组集返回其总订货量。可能我们用GROUPING SETS需要4行，然后使用ROLLUP却只需要一行：group by rollup(YEAR(orderdate),MONTH(orderdate),DAY(orderdate));
完整SQL查询如下：
--3.3ROLLUP从属子句 select YEAR(orderdate) as orderyear, MONTH(orderdate) as ordermonth, DAY(orderdate) as orderday, SUM(qty) as sumqty from dbo.Orders group by rollup(YEAR(orderdate),MONTH(orderdate),DAY(orderdate));
执行结果如下图所示：
（4）GROUPING_ID函数
如果一个查询定义了多个分组集，还想把结果行和分组集关联起来，也就是说，为每个结果行标注它是和哪个分组集关联的。SQL Server 2008中引入了一个GROUPING_ID函数，简化了关联结果行和分组集的处理，可以容易地计算出每一行和哪个分组集相关联。
例如，继续上面的例子，我们想要将empid,custid作为输入：
select grouping_id(empid,custid) as groupingset, empid, custid, SUM(qty) as sumqty from dbo.Orders group by cube(empid,custid);View Code
执行结果中会出现groupingset为0,1,2,3，分别代表了empid,custid的4个可能的分组集（(empid,custid),(empid),(custid),()）。
① INSERT VALUES语句 ：这个语句恐怕我们再熟悉不过了把，在任何一本数据库的书上面都可以看到这个语句的身影。
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid) VALUES(10001, ‘20090212‘, 3, ‘A‘);
需要了解的是，前面也提到过，SQL Server 2008增强了VALUES语句的功能，允许在一条语句中指定由逗号分隔开的多行记录。例如下面的语句向Orders中插入了4行数据：
INSERT INTO dbo.Orders (orderid, orderdate, empid, custid) VALUES (10003, ‘20090213‘, 4, ‘B‘), (10004, ‘20090214‘, 1, ‘A‘), (10005, ‘20090213‘, 1, ‘C‘), (10006, ‘20090215‘, 3, ‘C‘);View Code
② INSERT SELECT语句 ：将一组由SELECT查询返回的结果行插入到目标表中。
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid) SELECT orderid, orderdate, empid, custid FROM TSQLFundamentals2008.Sales.Orders WHERE shipcountry = ‘UK‘;
③ INSERT EXEC语句：将存储过过程或动态SQL批处理返回的结果集插入目标表。
下面的示例演示了如何执行存储过程usp_getorders并将结果插入到Orders表中：
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid) EXEC TSQLFundamentals2008.Sales.usp_getorders @country = ‘France‘;
④ SELECT INTO语句：它会创建一个目标表，并用查询返回的结果来填充它。需要注意的是：它不是一个标准的SQL语句（即不是ANSI SQL标准的一部分），不能用这个语句向已经存在的表中插入数据。
--保证目标表不存在 IF OBJECT_ID(‘dbo.Orders‘, ‘U‘) IS NOT NULL DROP TABLE dbo.Orders; SELECT orderid, orderdate, empid, custid INTO dbo.Orders FROM TSQLFundamentals2008.Sales.Orders;
⑤ BULK INSERT语句：用于将文件中的数据导入一个已经存在的表，需要制定目标表、源文件以及一些其他的选项。
下面的栗子演示了如何将文件"C:\testdata\orders.txt"中的数据容量插入（bulk insert）到Orders表，同时还指定了文件类型为字符格式，字段终止符为逗号，行终止符为换行符（\t）：
BULK INSERT dbo.Orders FROM ‘C:\testdata\orders.txt‘ WITH ( DATAFILETYPE = ‘char‘, FIELDTERMINATOR = ‘,‘, ROWTERMINATOR = ‘\n‘ );
① DELETE语句：标准SQL语句，大家最常见的用法。
DELETE FROM dbo.Orders WHERE orderdate < ‘20070101‘;
② TRUNCATE语句：不是标准的SQL语句，永于删除表中的所有行，不需要过滤条件。
Tip：TRUNCATE与DELETE在性能上差异巨大，对一个百万行级记录的表，TRUNCATE几秒内就可以解决，而DELETE可能需要几分钟。因为TRUNCATE会以最小模式记录日志，而DELETE则以完整模式记录日志。所以，各位，谨慎使用TRUNCATE。因此，我们可以创建一个虚拟表（Dummy Table），让虚拟表包含一个指向产品表的外键，这样就可以保护产品表了。
③ 基于联接的DELETE：也不是标准SQL语句，可以根据另一个表中相关行的属性定义的过滤器来删除表中的数据行。
例如，下面语句用以删除美国客户下的订单：
DELETE FROM O FROM dbo.Orders AS O JOIN dbo.Customers AS C ON O.custid = C.custid WHERE C.country = N‘USA‘;
当然，如果要使用标准SQL语句，也可以采用下面的方式：
DELETE FROM dbo.Orders WHERE EXISTS (SELECT * FROM dbo.Customers AS C WHERE Orders.custid = C.custid AND C.country = N‘USA‘);
① UPDATE语句：不解释了，大家都在用
下面来看两个不一样的栗子，第一个是关于同时操作的性质。看看下面的UPDATE语句：
UPDATE dbo.T1 SET col1 = col1 + 10, col2 = col1 + 10;
假设T1表中的col1列为100，col2列为200。在计算后是多少呢？
答案揭晓：col=110,col=110。
再来看一个栗子，假设我们要实现两个数的交换该怎么做？我们可能迫不及待的说出临时变量。然而，在SQL中所有赋值表达式好像都是同时计算的，解决这个问题就不需要临时变量了。
UPDATE dbo.T1 SET col1 = col2, col2 = col1;
② 基于联接的UPDATE语句：同样不是SQL标准语法，联接在此与基于联接的DELETE一样是起到过滤作用。
UPDATE OD SET discount = discount + 0.05 FROM dbo.OrderDetails AS OD JOIN dbo.Orders AS O ON OD.orderid = O.orderid WHERE custid = 1;
同样，要使用标准SQL语法的话，可以用子查询替代联接：
UPDATE dbo.OrderDetails SET discount = discount + 0.05 WHERE EXISTS (SELECT * FROM dbo.Orders AS O WHERE O.orderid = OrderDetails.orderid AND custid = 1);
③ 赋值UPDATE：这是T-SQL特有的语法，可以对表中的数据进行更新的同时为变量赋值。你不需要使用单独的UPDATE和SELECT语句，就能完成同样的任务。
假设我们有一个表Sequence，它只有一列val，全是序号数字。我们可以通过赋值UPDATE得到一个新的序列值：
DECLARE @nextval AS INT; UPDATE Sequence SET @nextval = val = val + 1; SELECT @nextval;
SQL Server 2008引入了一个叫做MERGE的语句，它能在一条语句中根据逻辑条件对数据进行不同的修改操作（INSERT/UPDATE/DELETE）。MERGE语句是SQL标准的一部分，而T-SQL版本的MERGE语句也增加了一些非标准的扩展。
下面我们看看如何合并，首先我们准备两张表Customers和CustomersStage：
--merge data USE tempdb; IF OBJECT_ID(‘dbo.Customers‘, ‘U‘) IS NOT NULL DROP TABLE dbo.Customers; GO CREATE TABLE dbo.Customers ( custid INT NOT NULL, companyname VARCHAR(25) NOT NULL, phone VARCHAR(20) NOT NULL, address VARCHAR(50) NOT NULL, CONSTRAINT PK_Customers PRIMARY KEY(custid) ); INSERT INTO dbo.Customers(custid, companyname, phone, address) VALUES (1, ‘cust 1‘, ‘(111) 111-1111‘, ‘address 1‘), (2, ‘cust 2‘, ‘(222) 222-2222‘, ‘address 2‘), (3, ‘cust 3‘, ‘(333) 333-3333‘, ‘address 3‘), (4, ‘cust 4‘, ‘(444) 444-4444‘, ‘address 4‘), (5, ‘cust 5‘, ‘(555) 555-5555‘, ‘address 5‘); IF OBJECT_ID(‘dbo.CustomersStage‘, ‘U‘) IS NOT NULL DROP TABLE dbo.CustomersStage; GO CREATE TABLE dbo.CustomersStage ( custid INT NOT NULL, companyname VARCHAR(25) NOT NULL, phone VARCHAR(20) NOT NULL, address VARCHAR(50) NOT NULL, CONSTRAINT PK_CustomersStage PRIMARY KEY(custid) ); INSERT INTO dbo.CustomersStage(custid, companyname, phone, address) VALUES (2, ‘AAAAA‘, ‘(222) 222-2222‘, ‘address 2‘), (3, ‘cust 3‘, ‘(333) 333-3333‘, ‘address 3‘), (5, ‘BBBBB‘, ‘CCCCC‘, ‘DDDDD‘), (6, ‘cust 6 (new)‘, ‘(666) 666-6666‘, ‘address 6‘), (7, ‘cust 7 (new)‘, ‘(777) 777-7777‘, ‘address 7‘); -- Query tables SELECT * FROM dbo.Customers; SELECT * FROM dbo.CustomersStage;View Code
执行结果如下图所示：
现在我们想要增加还不存在的客户，并更新已经存在的客户。源表：CustomersStage，目标表：Customers。
MERGE INTO dbo.Customers AS TGT USING dbo.CustomersStage AS SRC ON TGT.custid = SRC.custid WHEN MATCHED THEN UPDATE SET TGT.companyname = SRC.companyname, TGT.phone = SRC.phone, TGT.address = SRC.address WHEN NOT MATCHED THEN INSERT (custid, companyname, phone, address) VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address);
谓词条件：TGT.custid=SRC.custid用于定义什么样的数据是匹配的，什么样的数据是不匹配的。
Tips：MERGE语句必须以分号结束，而对于T-SQL中的大多数其他语句来说是可选的。但是，推荐遵循最佳实践，以分号结束。
① 通过表表达式修改数据
-- 基于联接的UPDATE UPDATE OD SET discount = discount + 0.05 FROM dbo.OrderDetails AS OD JOIN dbo.Orders AS O ON OD.orderid = O.orderid WHERE custid = 1; -- 基于表表达式（这里是CTE）的UPDATE WITH C AS ( SELECT custid, OD.orderid, productid, discount, discount + 0.05 AS newdiscount FROM dbo.OrderDetails AS OD JOIN dbo.Orders AS O ON OD.orderid = O.orderid WHERE custid = 1 ) UPDATE C SET discount = newdiscount;
② 带有TOP选项的数据更新
-- 删除前50行 DELETE TOP(50) FROM dbo.Orders; -- 更新前50行 UPDATE TOP(50) dbo.Orders SET freight = freight + 10.00; -- 基于CTE删除前50行 WITH C AS ( SELECT TOP(50) * FROM dbo.Orders ORDER BY orderid ) DELETE FROM C; -- 基于CTE更新前50行 WITH C AS ( SELECT TOP(50) * FROM dbo.Orders ORDER BY orderid DESC ) UPDATE C SET freight = freight + 10.00;
在某些场景中，我们希望能够从修改过的行中返回数据，这时就可以使用OUTPUT子句。SQL Server 2005引入了OUTPUT子句，通过在修改语句中添加OUTPUT子句，就可以实现从修改语句中返回数据的功能。
① 带有OUTPUT的INSERT语句
INSERT INTO dbo.T1(datacol) OUTPUT inserted.keycol, inserted.datacol SELECT lastname FROM TSQLFundamentals2008.HR.Employees WHERE country = N‘USA‘;
② 带有OUTPUT的DELETE语句
DELETE FROM dbo.Orders OUTPUT deleted.orderid, deleted.orderdate, deleted.empid, deleted.custid WHERE orderdate < ‘20080101‘;
③ 带有OUTPUT的UPDATE语句
UPDATE dbo.OrderDetails SET discount = discount + 0.05 OUTPUT inserted.productid, deleted.discount AS olddiscount, inserted.discount AS newdiscount WHERE productid = 51;
④ 带有OUTPUT的MERGE语句
MERGE INTO dbo.Customers AS TGT USING dbo.CustomersStage AS SRC ON TGT.custid = SRC.custid WHEN MATCHED THEN UPDATE SET TGT.companyname = SRC.companyname, TGT.phone = SRC.phone, TGT.address = SRC.address WHEN NOT MATCHED THEN INSERT (custid, companyname, phone, address) VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address) OUTPUT $action, inserted.custid, deleted.companyname AS oldcompanyname, inserted.companyname AS newcompanyname, deleted.phone AS oldphone, inserted.phone AS newphone, deleted.address AS oldaddress, inserted.address AS newaddress;
以上MERGE语句使用OUTPUT子句返回被修改过的行的新旧版本的值。对于INSERT操作不存在旧版本的值，因此所有deleted列的值都返回NULL。$action函数会告诉我们输出行是UPDATE还是由INSERT操作生成的。
考虑到这一章的内容比较多而且十分重要，书里边的栗子不多，打算多参考一些资料再将其单独归纳写一篇独立的文章，到时share给各位园友，敬请谅解。
（1）变量：DECLARE+SET/SELECT
DECLARE语句可以声明一个或多个变量，然后使用SET/SELECT语句可以把一个变量设置成指定的值。
① SET语句每次只能针对一个变量进行操作
--set方式 declare @i as int set @i=10; --SQL Server 2008可以在同一语句同时声明和初始化变量 declare
