T-SQL 学习笔记 Chapter 6 子查询、表表达式和排名函数（一）-优快云博客

子查询：标量子查询

相关子查询

多值子查询

独立子查询

表表达式：派生表和公用表表达式CTE

排名函数：行号 row number,

排名rank,

密度排名dense rank,

分组NTILE

1.标量独立子查询

SELECT orderid FROM Sales.Orders
WHERE empid = 
  (SELECT empid FROM HR.Employees
   -- also try with N'Kollar' and N'D%'
   WHERE lastname LIKE N'Davis');

SELECT orderid FROM Sales.Orders
WHERE empid = 
  (SELECT empid FROM HR.Employees
   -- also try with N'Kollar' and N'D%'
   WHERE lastname LIKE N'Davis');

子查询返回一条或者0条数据查询顺利执行，返回多条数据则报错。

使用group by 和distinct count 来解决关系除法的问题。

2.相关子查询

相关子查询是引用了在外部查询中出现的列的子查询。从逻辑上讲，子查询会为外部查询的每一行进行一次计算。

SELECT * FROM SYSDATABASES--查询所有的表

查询最近的订单

索引：

CREATE UNIQUE INDEX idx_eid_od_oid
  ON Sales.Orders(empid, orderdate, orderid);
CREATE UNIQUE INDEX idx_eid_od_rd_oid
  ON Sales.Orders(empid, orderdate, requireddate, orderid);
GO
DROP INDEX Sales.Orders.idx_eid_od_oid;
DROP INDEX Sales.Orders.idx_eid_od_rd_oid;

SELECT orderid, custid, empid, orderdate, requireddate
FROM Sales.Orders AS O1
WHERE orderdate =
  (SELECT MAX(orderdate)
   FROM Sales.Orders AS O2
   WHERE O2.empid = O1.empid)
  AND orderid =
  (SELECT MAX(orderid)
   FROM Sales.Orders AS O2
   WHERE O2.empid = O1.empid
     AND O2.orderdate = O1.orderdate);
 
-- Most recent order for each employee, nesting subqueries
-- Tiebreaker: max order id
 
SELECT orderid, custid, empid, orderdate, requireddate
FROM Sales.Orders AS O1
WHERE orderid =
  (SELECT MAX(orderid)
   FROM Sales.Orders AS O2
   WHERE O2.empid = O1.empid
     AND O2.orderdate =
       (SELECT MAX(orderdate)
        FROM Sales.Orders AS O3
        WHERE O3.empid = O1.empid));
 
-- Most recent order for each employee
-- Tiebreaker: max requireddate, max orderid
SELECT orderid, custid, empid, orderdate, requireddate
FROM Sales.Orders AS O1
WHERE orderdate =
  (SELECT MAX(orderdate)
   FROM Sales.Orders AS O2
   WHERE O2.empid = O1.empid)
  AND requireddate =
  (SELECT MAX(requireddate)
   FROM Sales.Orders AS O2
   WHERE O2.empid = O1.empid
     AND O2.orderdate = O1.orderdate)
  AND orderid =
  (SELECT MAX(orderid)
   FROM Sales.Orders AS O2
   WHERE O2.empid = O1.empid
     AND O2.orderdate = O1.orderdate
     AND O2.requireddate = O1.requireddate);

3.EXISTS

如何查看查询计划 https://www.cnblogs.com/knowledgesea/p/5005163.html

为社么not in和not exist 对于查询有空值的时候，结果不同

4.查询最小的缺失键值

5.不常用的ALL SOME ANY

6. 派生表

6.1 使用参数

每年由雇员3处理的客户数量

DECLARE @EMPID AS INT=3
 
SELECT Y ,count( DISTINCT cusTID) AS NUMBER FROM
(SELECT  year(orderdate) AS Y,CUSTID FROM  SALES.ORDERS WHERE EMPID=@EMPID)T
GROUP BY Y

6.2嵌套---不推荐

返回客户数量超过70的订单年份和该年份的客户数量

SELECT Y ,count( DISTINCT custid) AS NUMBER FROM
(select year(orderdate) y, custid from SALES.ORDERS) t
group by y having count( DISTINCT custid)>70

7.公用表表达式CTE

公用表表达式类似于派生表两者均无法使用ORDERBY 除非包含top，每一列都必须有列名

7.1两种声明方式

-- Inline column aliasing
WITH C AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;
 
-- External column aliasing
WITH C(orderyear, custid) AS
(
  SELECT YEAR(orderdate), custid
  FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;
GO

7.2 在CTE中使用变量

DECLARE @empid AS INT = 3;

WITH C AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
  WHERE empid = @empid
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;
GO

7.3多CTE CTE不能在内部定义另一个CTE 但是每一个CTE都可以引用前面的CTE

-- Defining multiple CTEs
WITH C1 AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
),
C2 AS
(
  SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
  FROM C1
  GROUP BY orderyear
)
SELECT orderyear, numcusts
FROM C2
WHERE numcusts > 70;

7.4多引用可以重复引用一个CTE

-- Multiple references
WITH YearlyCount AS
(
  SELECT YEAR(orderdate) AS orderyear,
    COUNT(DISTINCT custid) AS numcusts
  FROM Sales.Orders
  GROUP BY YEAR(orderdate)
)
SELECT Cur.orderyear, 
  Cur.numcusts AS curnumcusts, Prv.numcusts AS prvnumcusts,
  Cur.numcusts - Prv.numcusts AS growth
FROM YearlyCount AS Cur
  LEFT OUTER JOIN YearlyCount AS Prv
    ON Cur.orderyear = Prv.orderyear + 1;
GO

7.5可以通过CTE修改数据

7.5.1 删除重复客户

IF OBJECT_ID('Sales.CustomersDups') IS NOT NULL
  DROP TABLE Sales.CustomersDups;
GO

SELECT 
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
INTO Sales.CustomersDups
FROM Sales.Customers CROSS JOIN (VALUES(1),(2),(3)) AS Nums(n);

-- Modifying data through CTEs
WITH CustsDupsRN AS
(
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY custid ORDER BY (SELECT 0)) AS rn
  FROM Sales.CustomersDups
)
DELETE FROM CustsDupsRN
WHERE rn > 1;
GO

7.6视图和内联函数中使用CTE

-- UDF with CTE
IF OBJECT_ID('dbo.EmpYearCustCnt') IS NOT NULL
  DROP FUNCTION dbo.EmpYearCustCnt;
GO
CREATE FUNCTION dbo.EmpYearCustCnt(@empid AS INT) RETURNS TABLE
AS
RETURN
  WITH CEmpYearCustCnt AS
  (
    SELECT YEAR(orderdate) AS orderyear,
      COUNT(DISTINCT custid) AS numcusts
    FROM Sales.Orders
    WHERE empid = @empid
    GROUP BY YEAR(orderdate)
  )
  SELECT * FROM CEmpYearCustCnt;
GO

-- Querying UDF with CTE
SELECT * FROM dbo.EmpYearCustCnt(3) AS T;
GO