SQL基本查询语句（Northwind数据库）

1. 每月最后实际订单日期发生的订单

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate IN
  (SELECT MAX(OrderDate)
   FROM dbo.Orders
   GROUP BY CONVERT(CHAR(6), OrderDate, 112));
GO

思路：

首先按年月分组，截取字符串返回年+月，然后获取该月订单最大值，再进行筛选

 

2. 每个员工具有最大订单日期的订单

-- Orders with maximum OrderDate for each employee
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate 
FROM dbo.Orders AS O1
WHERE OrderDate =
  (SELECT MAX(OrderDate)
   FROM dbo.Orders AS O2
   WHERE O2.EmployeeID = O1.EmployeeID);

每个员工返回一行数据

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate 
FROM dbo.Orders AS O1
WHERE OrderDate =
  (SELECT MAX(OrderDate)
   FROM dbo.Orders AS O2
   WHERE O2.EmployeeID = O1.EmployeeID)
  AND OrderID =
  (SELECT MAX(OrderID)
   FROM dbo.Orders AS O2
   WHERE O2.EmployeeID = O1.EmployeeID
     AND O2.OrderDate = O1.OrderDate);

方法二：

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate 
FROM dbo.Orders AS O1
WHERE OrderID = (SELECT MAX(OrderID) FROM dbo.Orders AS O2
                  WHERE O2.EmployeeID = O1.EmployeeID
                  AND O2.OrderDate = 
                  (SELECT MAX(OrderDate)
                   FROM dbo.Orders AS O3
                   WHERE O3.EmployeeID = O1.EmployeeID));

完整查询如下：

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate 
FROM dbo.Orders AS O1
WHERE OrderDate =
  (SELECT MAX(OrderDate)
   FROM dbo.Orders AS O2
   WHERE O2.EmployeeID = O1.EmployeeID)
  AND RequiredDate =
  (SELECT MAX(RequiredDate)
   FROM dbo.Orders AS O2
   WHERE O2.EmployeeID = O1.EmployeeID
     AND O2.OrderDate = O1.OrderDate)
  AND OrderID =
  (SELECT MAX(OrderID)
   FROM dbo.Orders AS O2
   WHERE O2.EmployeeID = O1.EmployeeID
     AND O2.OrderDate = O1.OrderDate
     AND O2.RequiredDate = O1.RequiredDate);

 每个子查询必须关联到分组列，排序列和附加属性

 

创建别名 （内联别名和外部列别名）

-- Inline column aliasing
SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts
FROM (SELECT YEAR(OrderDate) AS OrderYear, CustomerID
      FROM dbo.Orders) AS D
GROUP BY OrderYear;

-- External column aliasing
SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts
FROM (SELECT YEAR(OrderDate), CustomerID
      FROM dbo.Orders) AS D(OrderYear, CustomerID)
GROUP BY OrderYear;
GO

 

比较每年和上一年的活动消费者数量

SELECT Cur.OrderYear, 
  Cur.NumCusts AS CurNumCusts, Prv.NumCusts AS PrvNumCusts,
  Cur.NumCusts - Prv.NumCusts AS Growth
FROM (SELECT YEAR(OrderDate) AS OrderYear,
        COUNT(DISTINCT CustomerID) AS NumCusts
      FROM dbo.Orders
      GROUP BY YEAR(OrderDate)) AS Cur
  LEFT OUTER JOIN
     (SELECT YEAR(OrderDate) AS OrderYear,
        COUNT(DISTINCT CustomerID) AS NumCusts
      FROM dbo.Orders
      GROUP BY YEAR(OrderDate)) AS Prv
    ON Cur.OrderYear = Prv.OrderYear + 1;
GO

方法二

-- Multiple references
WITH YearlyCount AS
(
  SELECT YEAR(OrderDate) AS OrderYear,
    COUNT(DISTINCT CustomerID) AS NumCusts
  FROM dbo.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

 With用法

-- Inline column aliasing
WITH C AS
(
  SELECT YEAR(OrderDate) AS OrderYear, CustomerID
  FROM dbo.Orders
)
SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts
FROM C
GROUP BY OrderYear;

-- External column aliasing
WITH C(OrderYear, CustomerID) AS
(
  SELECT YEAR(OrderDate) AS OrderYear, CustomerID
  FROM dbo.Orders
)
SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts
FROM C
GROUP BY OrderYear;
GO

 多With

WITH C1 AS
(
  SELECT YEAR(OrderDate) AS OrderYear, CustomerID
  FROM dbo.Orders
),
C2 AS
(
  SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts
  FROM C1
  GROUP BY OrderYear
)
SELECT OrderYear, NumCusts
FROM C2
WHERE NumCusts > 70;

去除重复的消费者数据

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

WITH CrossCustomers AS
(
  SELECT 1 AS c, C1.*
  FROM dbo.Customers AS C1, dbo.Customers AS C2
)
SELECT ROW_NUMBER() OVER(ORDER BY c) AS KeyCol,
  CustomerID, CompanyName, ContactName, ContactTitle, Address,
  City, Region, PostalCode, Country, Phone, Fax
INTO dbo.CustomersDups
FROM CrossCustomers;

CREATE UNIQUE INDEX idx_CustomerID_KeyCol
  ON dbo.CustomersDups(CustomerID, KeyCol);
GO
select * from CustomersDups

-- Modifying data through CTEs
WITH JustDups AS
(
  SELECT * FROM dbo.CustomersDups AS C1
  WHERE KeyCol < 
    (SELECT MAX(KeyCol) FROM dbo.CustomersDups AS C2
     WHERE C2.CustomerID = C1.CustomerID)
)
DELETE FROM JustDups;
GO

 With视图和函数

-- View with CTE
IF OBJECT_ID('dbo.VYearCnt') IS NOT NULL
  DROP VIEW dbo.VYearCnt;
GO
CREATE VIEW dbo.VYearCnt
AS
WITH YearCnt AS
(
  SELECT YEAR(OrderDate) AS OrderYear,
    COUNT(DISTINCT CustomerID) AS NumCusts
  FROM dbo.Orders
  GROUP BY YEAR(OrderDate)
)
SELECT * FROM YearCnt;
GO

-- Querying view with CTE
SELECT * FROM dbo.VYearCnt;
GO

-- UDF with CTE
IF OBJECT_ID('dbo.fn_EmpYearCnt') IS NOT NULL
  DROP FUNCTION dbo.fn_EmpYearCnt;
GO
CREATE FUNCTION dbo.fn_EmpYearCnt(@EmpID AS INT) RETURNS TABLE
AS
RETURN
  WITH EmpYearCnt AS
  (
    SELECT YEAR(OrderDate) AS OrderYear,
      COUNT(DISTINCT CustomerID) AS NumCusts
    FROM dbo.Orders
    WHERE EmployeeID = @EmpID
    GROUP BY YEAR(OrderDate)
  )
  SELECT * FROM EmpYearCnt;
GO

-- Querying UDF with CTE
SELECT * FROM dbo.fn_EmpYearCnt(3);
GO

 With 递归

-- Create index for recursive CTE
CREATE UNIQUE INDEX idx_mgr_emp_ifname_ilname
  ON dbo.Employees(ReportsTo, EmployeeID)
  INCLUDE(FirstName, LastName);

-- Recursive CTE returning subordinates of employee 5 in all levels
WITH EmpsCTE AS
(
  SELECT EmployeeID, ReportsTo, FirstName, LastName
  FROM dbo.Employees
  WHERE EmployeeID =2

  UNION ALL

  SELECT EMP.EmployeeID, EMP.ReportsTo, EMP.FirstName, EMP.LastName
  FROM EmpsCTE AS MGR
    JOIN dbo.Employees AS EMP
      ON EMP.ReportsTo = MGR.EmployeeID
)
SELECT * FROM EmpsCTE;

-- Cleanup
DROP INDEX dbo.Employees.idx_mgr_emp_ifname_ilname;

第一个查询用于定位