汇总SQL Server里的相关运算符、子句、谓词等

汇总SQL Server里的相关运算符、子句、谓词等

（后续我会往后追加并不断对现有的进行完善和扩展）

 

◆ TOP
1）TOP一般与ORDER BY结合使用，否则TOP出来的结果集没太大意义，除非您另有它意。

2）TOP返回数可以是变量，但必须用括号括入
3）结合WITH TIES谓词选项，如果您返回4行，但最后1行有2条相同的结果，那么您TOP

4，最后1行就只是随意返回1行，保证不了结果集的正确性，如果您指定了WITH TIES，

则返回5行，ORDER BY后将最后2条相同的结果都返回，用法：TOP number WITH TIES
4）子查询里用到TOP必须要有ORDER BY
5）还有一种用意，返回结果集的百分比：TOP number PERCENT

 

◆ APPLY运算符
APPLY表运算符的作用是把右表表达式应用到左表表达式中的每一行。APPLY运算符有两

种形式：CROSS APPLY和OUTER APPLY。
举个例子：左表为供应商，右表为产品。我想得到每个供应商下的最高价的两个产品
SELECT S.供应商名称,P.产品名称,P.单价 FROM 供应商 AS S
CROSS APPLY
（
 SELECT TOP 2 P.产品名称,P.单价 FROM 产品 AS P
 WHERE P.供应商ID=S.供应商ID
 ORDER BY 单价 DESC
）
此时没有产品的供应商就不会列出，用OUTER APPLY就会列出全部供应商，没有产品的产

品相关列此时显示为NULL，这就类似LEFT OUTER JOIN。

 

◆ EXISTS和IN区别
使用EXISTS和IN谓词，对于在逻辑上相等的查询，一般会生成相同的计划，否则不然。先看看执行结果

创建测试表和数据：

IF OBJECT_ID('Customers','U') IS NOT NULL DROP TABLE Customers
GO
CREATE TABLE Customers
(
    CustID int IDENTITY(1,1) NOT NULL,
    CustName nvarchar(100) NULL,
    CustAddress nvarchar(100) NULL 
);
GO
IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders
GO
CREATE TABLE Orders
(
    OrderID int IDENTITY(1,1) NOT NULL,
    CustID int NULL,
    OrderContent nvarchar(100) NULL
);
GO
--插入客户
INSERT INTO Customers(CustName,CustAddress) values('客户1','北京');
INSERT INTO Customers(CustName,CustAddress) values('客户2','上海');
INSERT INTO Customers(CustName,CustAddress) values('客户3','天津');
INSERT INTO Customers(CustName,CustAddress) values('客户4','湖北');
--插入订单
INSERT INTO Orders(CustID,OrderContent) values(1,'2009年1月份订单');
INSERT INTO Orders(CustID,OrderContent) values(1,'2009年2月份订单');
INSERT INTO Orders(CustID,OrderContent) values(2,'2010年1月份订单');
INSERT INTO Orders(CustID,OrderContent) values(3,'2011年1月份订单');

 

SELECT CustID,CustName,CustAddress
FROM Customers AS C
WHERE CustAddress=N'湖北'
    AND NOT EXISTS
    (SELECT * FROM Orders AS O WHERE O.CustID=C.CustID)

SELECT CustID,CustName,CustAddress
FROM Customers AS C
WHERE CustAddress=N'湖北'
    AND CustID NOT IN(SELECT CustID FROM Orders)

查询以上两条SQL语句，结果一样

INSERT INTO Orders(CustID,OrderContent) values(NULL,'2010年1月份订单');

再插入一条Orders表CustID列为空的行，然后再执行以上两个查询，结果发生了变化。

下面我通过另外两个表（由于这两上表的创建，还有索引和数据比较多，为了代码清洁在此不提供脚本），分别用EXISTS和IN来执行，得到的执行计划则不同。如果将NOT去掉执行计划相同，在此只显示不同执行计划的截图，目的只是说明他们的区别。

上图用NOT EXISTS显示的执行计划

上图用NOT IN显示的执行计划

 

针对以上例子得出以下结论：

首先从性能上，EXISTS，只要子查询中存在一行，立即终止查询，（特别说明一下非本文知识点：只要在CustID上建立索引，在这里非常有帮助，它能直接访问Orders表中具有特定CustID值的行）；IN则扫描或查找整个Orders表，从性能上讲不言自明。
另外，EXISTS根据子查询是返回至少1行，还是没有返回任何行，该谓词会返回TRUE或FALSE，它不同于IN，无论输入子查询是否返回行，EXISTS都不会返回UNKNOWN。
IN返回TRUE和FALSE，但遇到NULL值时，会返回UNKNOWN，如果用IN则返回UNKNOWN，如果用NOT IN则返回NOT NUKNOWN，这个结果还是等于NUKNOWN，所以返回空行。

要想结果一样，对IN用两种方法解决，一是设置CustID为非空，另一种是在IN子查询语句后加非空条件筛选。
SQL里有时这种细微的差别会导致错误的结果，会造成不必要的麻烦。

 

◆ OVER子句
OVER与ROW_NUMBER函数结合使用
OVER与聚合函数结合使用
OVER与ROWS子句结合使用（以后再补）

先创建测试表和数据：

IF OBJECT_ID('dbo.Sales','U') IS NOT NULL
  DROP TABLE dbo.Sales;
GO
CREATE TABLE dbo.Sales
(
  empid VARCHAR(10) NOT NULL PRIMARY KEY,
  mgrid VARCHAR(10) NOT NULL,
  qty   INT         NOT NULL
);

INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES
  ('A', 'Z', 300),
  ('B', 'X', 100),
  ('C', 'X', 200),
  ('D', 'Y', 200),
  ('E', 'Z', 250),
  ('F', 'Z', 300),
  ('G', 'X', 100),
  ('H', 'Y', 150),
  ('I', 'X', 250),
  ('J', 'Z', 100),
  ('K', 'Y', 200);

CREATE INDEX idx_qty_empid ON dbo.Sales(qty, empid);
CREATE INDEX idx_mgrid_qty_empid ON dbo.Sales(mgrid, qty, empid);
GO

SELECT empid, mgrid, qty,
  ROW_NUMBER() OVER(ORDER BY mgrid) AS rownum
FROM dbo.Sales
ORDER BY mgrid;

SELECT empid, mgrid, qty,
  ROW_NUMBER() OVER(PARTITION BY mgrid ORDER BY qty) AS rownum
FROM dbo.Sales
ORDER BY mgrid,qty,empid;

SELECT empid,mgrid,
    SUM(qty) OVER(PARTITION BY mgrid) AS mgrGroup
FROM dbo.Sales

执行以上三条查询，能看出不同的结果。

第一个语句是利用ROW_NUMBER函数与OVER结合使用；
第二个语句是分组得到行号；
第三个语句是分组利用聚合函数汇总。

 

◆ Pivoting透视转换

先创建测试表和数据：

IF OBJECT_ID('dbo.Inbound','U') IS NOT NULL
  DROP TABLE dbo.Inbound;
GO
CREATE TABLE Inbound
(
    ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    WareHouseName nvarchar(50) NULL,
    MaterialName nvarchar(50) NULL,
    Quantity decimal(12,2) NULL,
    Price decimal(12,2) NULL
)
--注意单价（万）为单位
INSERT INTO Inbound(WareHouseName,MaterialName,Quantity,Price) values('北京库','迈巴赫',10,5000);
INSERT INTO Inbound(WareHouseName,MaterialName,Quantity,Price) values('上海库','迈巴赫',5,5000);
INSERT INTO Inbound(WareHouseName,MaterialName,Quantity,Price) values('湖北库','迈巴赫',2,5000);
INSERT INTO Inbound(WareHouseName,MaterialName,Quantity,Price) values('北京库','阿斯顿马丁',10,3000);
INSERT INTO Inbound(WareHouseName,MaterialName,Quantity,Price) values('上海库','阿斯顿马丁',5,3000);
INSERT INTO Inbound(WareHouseName,MaterialName,Quantity,Price) values('湖北库','阿斯顿马丁',3,3000);

WITH T AS
(
    SELECT WareHouseName,SUM(Quantity) AS Quantity FROM Inbound GROUP BY WareHouseName
)
SELECT [北京库],[上海库],[湖北库]
FROM T
    PIVOT(SUM(Quantity) FOR WareHouseName
    IN([北京库],[上海库],[湖北库])) AS P;

以上是通过透视转换的表运算符（PIVOT），将行汇总后转列。

也可以更活点，列名动态创建：

--先创建一个测试仓库表
IF OBJECT_ID('dbo.WareHouse','U') IS NOT NULL
  DROP TABLE dbo.WareHouse;
GO
CREATE TABLE WareHouse
(
    WareHouseID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    WareHouseName nvarchar(50) NULL
)
INSERT INTO WareHouse(WareHouseName) values('北京库');
INSERT INTO WareHouse(WareHouseName) values('上海库');
INSERT INTO WareHouse(WareHouseName) values('湖北库');

--动态创建列后进行行转列。
--PIVOT不支持动态透视转换，所以需要自己动态地构造查询字符串
DECLARE @NAME NVARCHAR(50)
SET @NAME=''
SELECT @NAME = @NAME + ',' + WareHouseName FROM WareHouse
SET @NAME = STUFF(@NAME, 1, 1, '')

DECLARE @STR NVARCHAR(MAX)
SET @STR='
WITH T AS
(
    SELECT WareHouseName,SUM(Quantity) AS Quantity FROM Inbound GROUP BY WareHouseName
)
SELECT '+@NAME+'
FROM T
    PIVOT(SUM(Quantity) FOR WareHouseName
    IN('+@NAME+')) AS P;'
EXEC(@STR)

执行结果如下：

 

（待扩展和完善......）

转载于:https://www.cnblogs.com/PongorXi/archive/2012/07/03/2575245.html