Transact-SQL 笔记

SELECT OrderDate, COUNT(OrderID) AS Orders
FROM Sales.SalesOrder
WHERE Status = 'Shipped'
GROUP BY OrderDate
HAVING COUNT(OrderID) > 1
ORDER BY OrderDate DESC;

FROM Sales.SalesOrder
WHERE Status = 'Shipped'
GROUP BY OrderDate
HAVING COUNT(OrderID) > 1
SELECT OrderDate, COUNT(OrderID) AS Orders
ORDER BY OrderDate DESC;

SELECT CAST(ProductID AS varchar(4)) + ': ' + Name AS ProductName
FROM Production.Product;

/*
该查询的结果可能如下所示：

ProductName

680：HL 公路车架 - 黑色，58

706：HL 公路车架 - 红色，58

707：Sport-100 头盔，红色

708：Sport-100 头盔，黑色

...
*/

SELECT CAST(Size AS integer) As NumericSize
FROM Production.Product;

SELECT TRY_CAST(Size AS integer) As NumericSize
FROM Production.Product;

/*
这次的结果可能如下所示：

NumericSize

58

58

Null

Null

...
*/

SELECT CONVERT(varchar(4), ProductID) + ': ' + Name AS ProductName
FROM Production.Product;

/*
同样，此查询将返回转换为指定数据类型的值，如下所示：

ProductName

680：HL 公路车架 - 黑色，58

706：HL 公路车架 - 红色，58

707：Sport-100 头盔，红色

708：Sport-100 头盔，黑色

...
*/

SELECT SellStartDate,
       CONVERT(varchar(20), SellStartDate) AS StartDate,
       CONVERT(varchar(10), SellStartDate, 101) AS FormattedStartDate 
FROM SalesLT.Product;

/*
该查询的结果可能如下所示：
SellStartDate          StartDate             FormattedStartDate
2002-06-01T00:00:00    Jun 1 2002 12:00AM    6/1/2002
2002-06-01T00:00:00    Jun 1 2002 12:00AM    6/1/2002
2005-07-01T00:00:00    Jul 1 2005 12:00AM    2005/7/1
2005-07-01T00:00:00    Jul 1 2005 12:00AM    2005/7/1
...                    ...                   ...
*/

SELECT PARSE('01/01/2021' AS date) AS DateValue,
   PARSE('$199.99' AS money) AS MoneyValue;

/*
该查询的结果如下所示：

DateValue                MoneyValue

2021-01-01T00:00:00      199.99
*/

SELECT ProductID,  '$' + STR(ListPrice) AS Price
FROM Production.Product;

/*
结果将如下所示：

ProductID                价格

680                      $1432.00

706                      $1432.00

707                      $35.00

...                      ...
*/

/* 注意
STR(RevisionNumber, 1)
SO71895 (2)                   2008.06.01

STR(RevisionNumber)
SO71938 (         2)                   2008.06.01

SELECT FirstName,
      ISNULL(MiddleName, 'None') AS MiddleIfAny,
      LastName
FROM Sales.Customer;

/*
FirstName            MiddleIfAny          LastName
奥兰多                N.                   Gee
Keith                无                   Howard
...                  ...                  ...
*/

SELECT OrderID,
       ISNULL(TRY_CONVERT(decimal(10, 2), Amount), 0) AS ValidAmount
FROM Sales.Order;

/*
示例：
假设 Sales.Order 表中的数据如下：

OrderID Amount
1   100.50
2   NULL
3   'Invalid'
4   150.75
执行上述查询后，你会得到：

OrderID ValidAmount
1   100.50
2   0.00
3   0.00
4   150.75
*/

SELECT COALESCE ( expression1, expression2, [ ,...n ] )

SELECT EmployeeID,
      COALESCE(HourlyRate * 40,
                WeeklySalary,
                Commission * SalesQty) AS WeeklyEarnings
FROM HR.Wages;

/*
结果可能如下所示：

EmployeeID             WeeklyEarnings

1                      899.76

2                      1001.00

3                      1298.77

...                    ...
*/

SELECT SalesOrderID,
      ProductID,
      UnitPrice,
      NULLIF(UnitPriceDiscount, 0) AS Discount
FROM Sales.SalesOrderDetail;

/*
结果可能如下所示：

销售订单 ID         ProductID          单价            折扣

71774              836                356.898        Null

71780              988                112.998        0.4               

71781              748                818.7          Null

71781              985                112.998        0.4

...                ...                ...            ...

INSERT [INTO] <Table> [(column_list)]
VALUES ([ColumnName or an expression or DEFAULT or NULL],…n)

INSERT INTO Sales.Promotion (PromotionName,StartDate,ProductModelID,Discount,Notes)
VALUES
('Clearance Sale', '01/01/2021', 23, 0.1, '10% discount');
-- 对于上面的示例，可以省略列清单，因为我们按正确的顺序为每列提供一个值：

-- 假设表定义为：当前日期的默认值应用于 StartDate 列，并且 Notes 列允许 NULL 值。 可以明确指示想要使用这些值，如下所示：

INSERT INTO Sales.Promotion
VALUES
('Pull your socks up', DEFAULT, 24, 0.25, NULL);

/*或者，可以省略 INSERT 语句中的值，如果省略，则使用默认值（如果已定义），如果没有默认值，但列允许 NULL 值，则将插入 NULL。 如果没有为所有列提供值，则必须包含列清单，指出为哪些列提供了值。*/

INSERT INTO Sales.Promotion (PromotionName, ProductModelID, Discount)
VALUES
('Caps Locked', 2, 0.2);

/* INSERT VALUES 语句除了能够一次插入一行之外，还可用于插入多行，方法是提供多个逗号分隔的值集。 值集也用逗号分隔，如下所示：
(col1_val,col2_val,col3_val),
(col1_val,col2_val,col3_val)
*/
-- 这一值列表称为表值构造函数。 下面的示例使用表值构造函数将另外两行插入表中：

INSERT INTO Sales.Promotion
VALUES
('The gloves are off!', DEFAULT, 3, 0.25, NULL),
('The gloves are off!', DEFAULT, 4, 0.25, NULL);

INSERT [INTO] <table or view> [(column_list)]
SELECT <column_list> FROM <table_list>...;

-- 以下示例将为名为 Get Framed 的新促销插入多行，方法是：从 Production.ProductModel（这是一个表，列出了名称中包含“frame”的所有模型）中检索模型 ID 和模型名称。

INSERT INTO Sales.Promotion (PromotionName, ProductModelID, Discount, Notes)
SELECT DISTINCT 'Get Framed', m.ProductModelID, 0.1, '10% off ' + m.Name
FROM Production.ProductModel AS m
WHERE m.Name LIKE '%frame%';

-- 与子查询不同，与 INSERT 一起使用的嵌套 SELECT 不用括号括起。

SELECT SalesOrderID, CustomerID, OrderDate, PurchaseOrderNumber, TotalDue
INTO Sales.Invoice
FROM Sales.SalesOrderHeader;

 SELECT SalesOrderID, OrderDate,
     CASE
         WHEN ShipDate IS NULL THEN 'Awaiting Shipment'
         ELSE 'Shipped'
     END AS ShippingStatus
 FROM SalesLT.SalesOrderHeader;

SELECT<select_list>
FROM <table_source>
ORDER BY <order_by_list> [ASC|DESC];

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
ORDER BY Category ASC, Price DESC;

SELECT TOP (N) <column_list>
FROM <table_source>
WHERE <search_condition>
ORDER BY <order list> [ASC|DESC];

SELECT TOP 10 Name, ListPrice
FROM Production.Product
ORDER BY ListPrice DESC;

/*
结果应如下所示：
名称                      ListPrice

Road-150 Red, 62          3578.27

Road-150 Red, 44          3578.27

Road-150 Red, 48          3578.27

Road-150 Red, 52          3578.27

Road-150 Red, 56          3578.27

Mountain-100 Silver, 38   3399.99

Mountain-100 Silver, 42   3399.99

Mountain-100 Silver, 44   3399.99

Mountain-100 Silver, 48   3399.99

Mountain-100 Black, 38    3374.99
*/

SELECT TOP 10 WITH TIES Name, ListPrice
FROM Production.Product
ORDER BY ListPrice DESC;

/*
修改后的查询将返回以下结果：

名称                    ListPrice

Road-150 Red, 62        3578.27

Road-150 Red, 44        3578.27

Road-150 Red, 48        3578.27

Road-150 Red, 52        3578.27

Road-150 Red, 56        3578.27

Mountain-100 Silver, 38 3399.99

Mountain-100 Silver, 42 3399.99

Mountain-100 Silver, 44 3399.99

Mountain-100 Silver, 48 3399.99

Mountain-100 Black, 38  3374.99

Mountain-100 Black, 42  3374.99

Mountain-100 Black, 44  3374.99

Mountain-100 Black, 48  3374.99

SELECT TOP 10 PERCENT Name, ListPrice
FROM SalesLT.Product
ORDER BY ListPrice DESC;

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
[FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY]

SELECT ProductID, ProductName, ListPrice
FROM Production.Product
ORDER BY ListPrice DESC 
OFFSET 0 ROWS --Skip zero rows
FETCH NEXT 10 ROWS ONLY; --Get the next 10

SELECT ProductID, ProductName, ListPrice
FROM Production.Product
ORDER BY ListPrice DESC 
OFFSET 10 ROWS --Skip 10 rows
FETCH NEXT 10 ROWS ONLY; --Get the next 10

SELECT City, CountryRegion
FROM Production.Supplier
ORDER BY CountryRegion, City;

/*
该查询可能返回如下所示的结果：

城市                  CountryRegion

Aurora                加拿大

Barrie                加拿大

Brampton              加拿大

Brossard              加拿大

Brossard              加拿大

Burnaby               加拿大

Burnaby               加拿大

Burnaby               加拿大

Calgary               加拿大

Calgary               加拿大

...                   ...
*/

SELECT ALL City, CountryRegion
FROM Production.Supplier
ORDER BY CountryRegion, City;

SELECT DISTINCT City, CountryRegion
FROM Production.Supplier
ORDER BY CountryRegion, City;
/*
使用 DISTINCT 时，示例仅返回 SELECT 列表中值的其中一个唯一组合：

城市             CountryRegion

Aurora           加拿大

Barrie           加拿大

Brampton         加拿大

Brossard         加拿大

Burnaby          加拿大

Calgary          加拿大

...              ...
*/

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ProductCategoryID = 2;

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ListPrice < 10.00;

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ProductName IS NOT NULL;

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ProductCategoryID = 2
    AND ListPrice < 10.00;

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE (ProductCategoryID = 2 OR ProductCategoryID = 3)
    AND (ListPrice < 10.00);

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ProductCategoryID = 2
    OR ProductCategoryID = 3
    OR ProductCategoryID = 4;

SELECT ProductCategoryID AS Category, ProductName
FROM Production.Product
WHERE ProductCategoryID IN (2, 3, 4);

SELECT Name, ListPrice
FROM SalesLT.Product
WHERE Name LIKE '%mountain%';

/*
% 通配符表示 0 个或更多字符的任何字符串，因此结果包括名称中任意位置有单词“mountain”的产品，如下所示：

名称                            ListPrice

山地自行车袜，M                  9.50

Mountain Bike Socks, L         9.50

HL Mountain Frame - Silver, 42 1364.0

HL Mountain Frame - Black, 42  1349.60

HL Mountain Frame - Silver, 38 1364.50

Mountain-100 Silver, 38        3399.99
*/

SELECT ProductName, ListPrice
FROM SalesLT.Product
WHERE ProductName LIKE 'Mountain Bike Socks, _';

/*
以下结果仅包括以“山地自行车袜子”开头的产品，以及后面的单个字符：

ProductName            ListPrice

山地自行车袜，M          9.50

Mountain Bike Socks, L 9.50

SELECT ProductName, ListPrice
FROM SalesLT.Product
WHERE ProductName LIKE 'Mountain-[0-9][0-9][0-9] %, [0-9][0-9]';

/*
该查询的结果可能如下所示：

ProductName                ListPrice

Mountain-100 Silver, 38    3399.99

Mountain-100 Silver, 42    3399.99

Mountain-100 Black, 38     3399.99

Mountain-100 Black, 42     3399.99

Mountain-200 Silver, 38    2319.99

Mountain-200 Silver, 42    2319.99

Mountain-200 Black, 38     2319.99

Mountain-200 Black, 42     2319.99

SELECT Name
FROM SalesLT.Product
WHERE SellEndDate BETWEEN '2006/1/1' AND '2006/12/31';

SELECT ProductNumber, Name
FROM SalesLT.Product
WHERE (Color = 'Black' OR Color = 'Red' OR Color = 'White') AND (Size = 'S' OR Size = 'M');

-- 等效

 SELECT ProductNumber, Name
 FROM SalesLT.Product
 WHERE Color IN ('Black','Red','White') AND Size IN ('S','M');

-- 这是最常见的情况，JOIN 是直接写在 FROM 子句之后，表示不同表之间的连接。

SELECT 
    p.ProductNumber, p.Name, c.Name AS CategoryName
FROM 
    SalesLT.Product p
JOIN 
    SalesLT.ProductCategory c ON p.ProductCategoryID = c.ProductCategoryID
ORDER BY 
    p.Name;

-- 在一些情况下，虽然 JOIN 仍然写在 FROM 子句中，但它可能被认为是单独的子句，特别是在某些 SQL 教程或文档中，强调 JOIN 是从表中“获取”数据的一种方式。

SELECT 
    p.ProductNumber, p.Name, c.Name AS CategoryName
FROM 
    SalesLT.Product p
WHERE 
    p.ListPrice > 100
JOIN 
    SalesLT.ProductCategory c ON p.ProductCategoryID = c.ProductCategoryID
ORDER BY 
    p.Name;

-- Products 表
ProductID | ProductName | CategoryID
----------------------------------
1         | Bike       | 10
2         | Helmet     | 20
3         | Tire       | 10
4         | Gloves     | 30

-- Categories 表
CategoryID | CategoryName
--------------------------
10         | Cycling
20         | Sports
30         | Accessories

SELECT p.ProductName, c.CategoryName
FROM Products p
JOIN Categories c ON p.CategoryID = c.CategoryID;
-- 通过联接操作，虚拟表中会生成新的行，包含 ProductName 和 CategoryName 字段。

ProductName | CategoryName
--------------------------
Bike        | Cycling
Tire        | Cycling
Helmet      | Sports
Gloves      | Accessories

SELECT p.ProductName, c.CategoryName
FROM Products p
LEFT JOIN Categories c ON p.CategoryID = c.CategoryID;
-- 通过左联接操作，Categories 表中的每一行都会尝试与 Products 表进行匹配。如果没有匹配项，Products 表中的行仍然会保留，只是 CategoryName 会返回 NULL。这里并没有行被“删除”，即使有某些商品没有匹配的类别，左联接会保留它们并用 NULL 填充。
ProductName | CategoryName
--------------------------
Bike        | Cycling
Tire        | Cycling
Helmet      | Sports
Gloves      | Accessories

-- 修改后的 Products 表
ProductID | ProductName | CategoryID
----------------------------------
1         | Bike       | 10
2         | Helmet     | 20
3         | Tire       | 10
4         | Gloves     | 30
5         | Unknown    | NULL  -- 新添加的没有类别的商品

SELECT p.ProductName, c.CategoryName
FROM Products p
INNER JOIN Categories c ON p.CategoryID = c.CategoryID;

-- 在这种情况下，Unknown 商品会被删除，因为它没有匹配的类别（NULL），INNER JOIN 只返回两个表中都有的匹配行。
ProductName | CategoryName
--------------------------
Bike        | Cycling
Tire        | Cycling
Helmet      | Sports
Gloves      | Accessories

SELECT p.ProductName, c.CategoryName
FROM Products p
LEFT JOIN Categories c ON p.CategoryID = c.CategoryID;

-- 这里 Unknown 商品的行被保留，虽然没有匹配的类别，但仍然保留了商品名称，并且 CategoryName 返回了 NULL。
ProductName | CategoryName
--------------------------
Bike        | Cycling
Tire        | Cycling
Helmet      | Sports
Gloves      | Accessories
Unknown     | NULL

SELECT p.ProductID, m.Name AS Model, p.Name AS Product
FROM SalesLT.Product AS p, SalesLT.ProductModel AS m
WHERE p.ProductModelID = m.ProductModelID;

SELECT p.ProductID, m.Name AS Model, p.Name AS Product
FROM SalesLT.Product AS p
JOIN SalesLT.ProductModel AS m
    ON p.ProductModelID = m.ProductModelID;

1) SELECT emp.FirstName, ord.Amount
2) FROM HR.Employee AS emp 
3) JOIN Sales.SalesOrder AS ord
4)      ON emp.EmployeeID = ord.EmployeeID;

SELECT emp.FirstName, ord.Amount
FROM HR.Employee AS emp 
INNER JOIN Sales.SalesOrder AS ord
    ON emp.EmployeeID = ord.EmployeeID;

/*
Orders 表：
OrderID OrderDate   CustomerID
1001    2025-01-01  A123
1002    2025-02-01  B456
1003    2025-03-01  C789

OrderDetails 表：
OrderID ProductID   Quantity    Price
1001    101         2           15.5
1001    102         1           25.0
1002    103         3           10.0
1003    104         5           12.0
1003    105         2           18.0
*/

-- 我们通常会使用单一列来联接这两个表，例如 OrderID，来查询每个订单的商品详情。
SELECT ord.OrderID, ord.OrderDate, od.ProductID, od.Quantity, od.Price
FROM Orders ord
INNER JOIN OrderDetails od
    ON ord.OrderID = od.OrderID;

/*
结果：
OrderID OrderDate   ProductID   Quantity    Price
1001    2025-01-01  101         2           15.5
1001    2025-01-01  102         1           25.0
1002    2025-02-01  103         3           10.0
1003    2025-03-01  104         5           12.0
1003    2025-03-01  105         2           18.0
*/

-- 复合联接则涉及多个列来进行匹配。在这种情况下，我们不仅需要 OrderID 来匹配订单，还可能需要 ProductID 来确定每个订单的具体商品。假设我们想要根据 OrderID 和 ProductID 两个条件来联合 Orders 表和 OrderDetails 表：

SELECT ord.OrderID, ord.OrderDate, od.ProductID, od.Quantity, od.Price
FROM Orders ord
INNER JOIN OrderDetails od
    ON ord.OrderID = od.OrderID
    AND ord.CustomerID = 'A123';  -- 复合联接，根据 OrderID 和 CustomerID

-- 假设我们不仅想要按 OrderID 匹配，而且还想根据 ProductID 匹配，来确保订单和每个商品的详细信息正确匹配：

SELECT ord.OrderID, ord.OrderDate, od.ProductID, od.Quantity, od.Price
FROM Orders ord
INNER JOIN OrderDetails od
    ON ord.OrderID = od.OrderID
    AND od.ProductID = 101;  -- 按照 OrderID 和 ProductID 两列进行复合联接

/*
结果：
OrderID OrderDate   ProductID   Quantity    Price
1001    2025-01-01  101         2           15.5
*/

-- FROM 子句
FROM HR.Employee AS emp 
INNER JOIN Sales.SalesOrder AS ord

/*
SQL Server 的优化器会尝试选择最有效的查询执行计划来执行查询，考虑多种因素，如索引、表大小、统计信息等。在执行 INNER JOIN 时，理论上，表的顺序不会影响最终的结果，因为 INNER JOIN 是一个对称的操作——无论哪个表先被列出，最终都会执行相同的联接操作。然而，优化器可以根据实际情况选择最优的执行顺序。
*/

-- 即使我们交换 FROM 和 JOIN 的表顺序，SQL Server 会根据其优化规则来决定执行顺序：
-- 表顺序交换
SELECT emp.FirstName, ord.Amount
FROM Sales.SalesOrder AS ord
INNER JOIN HR.Employee AS emp
    ON emp.EmployeeID = ord.EmployeeID;

-- 虽然表的顺序不同，但因为 INNER JOIN 是对称的，最终结果还是一样的。优化器会根据表的大小、索引等来选择最佳的执行顺序，而不一定是按你写的顺序来评估。

SELECT emp.FirstName, ord.Amount
FROM HR.Employee AS emp
LEFT OUTER JOIN Sales.SalesOrder AS ord
    ON emp.EmployeeID = ord.EmployeeID;

SELECT emp.FirstName, ord.Amount
FROM HR.Employee AS emp
LEFT JOIN Sales.SalesOrder AS ord
    ON emp.EmployeeID = ord.EmployeeID;

/*
Employee 表：
EmployeeID  Name
1           John
2           Jane
3           Alice

SalesOrder 表：
OrderID EmployeeID  Amount
101     1           200
102     2           150
103     4           300
*/

SELECT emp.Name, ord.Amount
FROM Employee emp
INNER JOIN SalesOrder ord ON emp.EmployeeID = ord.EmployeeID;

/*
返回结果：
Name    Amount
John    200
Jane    150
*/

SELECT emp.Name, ord.Amount
FROM Employee emp
FULL OUTER JOIN SalesOrder ord ON emp.EmployeeID = ord.EmployeeID;

/*
返回结果：
Name    Amount
John    200
Jane    150
Alice   NULL
NULL    300
*/

/*
Products 表：
ProductID   ProductName
1           Apple
2           Banana
3           Orange

Categories 表：
CategoryID  CategoryName
1           Fruit
2           Vegetable
*/

-- ANSI SQL-89 语法
SELECT Products.ProductName, Categories.CategoryName
FROM Products, Categories;

--  ANSI SQL-92 语法
SELECT Products.ProductName, Categories.CategoryName
FROM Products
CROSS JOIN Categories;

/*
返回的结果（笛卡尔积）：

ProductName CategoryName
Apple       Fruit
Apple       Vegetable
Banana      Fruit
Banana      Vegetable
Orange      Fruit
Orange      Vegetable
*/

SELECT <select_list>
FROM table1 AS t1
CROSS JOIN table2 AS t2;

-- 到目前为止，我们使用的联接涉及不同的表。 在某些情况下，你可能需要检索表中的行，并将其与同一表中的其他行进行比较。 例如，在人力资源应用程序中，“Employee”表可能包含有关每个员工的经理的信息，并将经理的 ID 存储在员工所在的那行中。 每个经理也列为一名员工。

/*
EmployeeID          FirstName           ManagerID

1                   Dan                 Null

2                   Aisha               1

3                   Rosie               1

4                   Naomi               3
*/

-- 要检索员工信息并将其与相关的经理进行匹配，可以在查询中使用该表两次，并将其与自身进行联接以实现查询目的。

SELECT emp.FirstName AS Employee, 
       mgr.FirstName AS Manager
FROM HR.Employee AS emp
LEFT OUTER JOIN HR.Employee AS mgr
  ON emp.ManagerID = mgr.EmployeeID;

-- 在该查询的结果中，每名员工一行，其中包含其经理的姓名。 公司的 CEO 没有经理。 为了在结果中包括 CEO，将使用外部联接，并且对于“ManagerID”字段没有匹配的“EmployeeID”字段的行，经理姓名将返回 NULL。

/*
Employee       Manager

Dan            Null

Aisha          Dan

Rosie          Dan

Naomi          Rosie
*/

INSERT INTO #Employees（[Name], Title, Manager) 
-- # 表示临时表，用于创建局部临时表，在会话结束后会自动删除。
-- [] 用于标识符，包裹表名或列名等数据库对象，防止它们与 SQL 保留字或特殊字符冲突。

-- 标量子查询 返回单个值，外部查询必须处理这个单个结果。通常用于 SELECT、WHERE、HAVING 或 ORDER BY 子句中，并且子查询必须保证只返回一行一列，否则会报错。
SELECT Name, ListPrice
FROM SalesLT.Product
WHERE ListPrice = (SELECT MAX(ListPrice) FROM SalesLT.Product);

-- 子查询：(SELECT MAX(ListPrice) FROM SalesLT.Product) 只返回一个值，即最高价格。
-- 外部查询：使用 WHERE ListPrice = 最高价格 过滤产品，找到最高价的产品。

-- 多值子查询 返回多行单列（相当于一个单列表格），外部查询必须能够处理多个值。常用于 IN、EXISTS、ANY、ALL 语句。
SELECT ProductID, Name
FROM SalesLT.Product
WHERE ProductID IN (SELECT ProductID FROM SalesLT.Product WHERE Color = 'Red');

-- 子查询：(SELECT ProductID FROM SalesLT.Product WHERE Color = 'Red') 可能返回多个 ProductID（多个值）。
-- 外部查询：使用 IN 关键字，匹配 ProductID 是否属于子查询返回的结果。

-- 自包含子查询 可以独立运行，不依赖外部查询中的任何列。它在外部查询执行前仅执行一次，然后将结果传递给外部查询。
SELECT Name, ListPrice
FROM SalesLT.Product
WHERE ListPrice > (SELECT MAX(ListPrice) FROM SalesLT.Product WHERE Color = 'Red');

-- 子查询 (SELECT MAX(ListPrice) FROM SalesLT.Product WHERE Color = 'Red')：该查询可以单独执行，它计算所有红色产品的最高价格。
-- 外部查询 WHERE ListPrice > 最高红色产品价格：过滤 ListPrice 高于红色产品最高价的产品。

-- 关联子查询 不能独立运行，它依赖外部查询中的列。子查询对外部查询中的每一行执行一次。
SELECT p1.Name, p1.Color, p1.ListPrice
FROM SalesLT.Product p1
WHERE p1.ListPrice > (
    SELECT AVG(p2.ListPrice)
    FROM SalesLT.Product p2
    WHERE p1.Color = p2.Color
);
-- 子查询 (SELECT AVG(p2.ListPrice) FROM SalesLT.Product p2 WHERE p1.Color = p2.Color)：
-- 它依赖外部查询的 p1.Color，计算相同 Color 的产品的平均价格。
-- 不能独立执行，因为 p1.Color 只有在外部查询运行时才有值。

-- 外部查询 WHERE p1.ListPrice > 该颜色的平均价格：
-- 逐行检查 p1.ListPrice 是否高于同色产品的平均价格。

SELECT MAX(SalesOrderID)
FROM Sales.SalesOrderHeader

SELECT SalesOrderID, ProductID, OrderQty
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 
   (SELECT MAX(SalesOrderID)
    FROM Sales.SalesOrderHeader);

-- 子查询没有返回任何行（空集）时的处理

SELECT c.CustomerName, 
       (SELECT TOP 1 o.Amount
        FROM Orders o
        WHERE o.CustomerID = c.CustomerID
        ORDER BY o.OrderDate DESC) AS LastOrderAmount
FROM Customers c;

-- 改进：用 COALESCE 处理 NULL

SELECT c.CustomerName, 
       COALESCE(
           (SELECT TOP 1 o.Amount
            FROM Orders o
            WHERE o.CustomerID = c.CustomerID
            ORDER BY o.OrderDate DESC), 0) AS LastOrderAmount
FROM Customers c;

/*
输出示例：
CustomerName    LastOrderAmount
Alice           100
Bob             NULL
Charlie         50

通过使用 COALESCE，当没有订单时，NULL 就被转换成了 0：

CustomerName    LastOrderAmount
Alice           100
Bob             0
Charlie         50
*/

-- 子查询返回多个列会出错
-- 假设我们有两个表：Employees 和 Departments。我们想要找出在某个部门工作的员工。以下是一个错误的示例，尝试让子查询返回多个列。

SELECT e.EmployeeName
FROM Employees e
WHERE (e.DepartmentID, e.JobTitle) IN (
    SELECT d.DepartmentID, d.ManagerID  -- 错误：子查询返回多个列
    FROM Departments d
    WHERE d.Location = 'New York'
);

-- 使用 EXISTS 处理多个列
-- EXISTS 关键字是一个例外，它可以用于检查子查询是否返回至少一行数据，而不要求子查询返回单列。即使子查询返回多个列，只要它返回至少一行，EXISTS 就会返回 TRUE。

SELECT e.EmployeeName
FROM Employees e
WHERE EXISTS (
    SELECT 1  -- `EXISTS` 不关心返回的列内容
    FROM Departments d
    WHERE e.DepartmentID = d.DepartmentID
    AND d.Location = 'New York'
);

-- EXISTS 子查询中，即使我们选择了多个列（在此例中，DepartmentID 和 Location），EXISTS 只关心子查询是否有行返回。如果子查询返回至少一行数据，那么外部查询就会返回相应的员工名称。
-- SELECT 1 是一种常见的写法，因为 EXISTS 只关心是否存在匹配的行，而不关心返回哪些列。

SELECT CustomerID, SalesOrderID
FROM Sales.SalesOrderHeader
WHERE CustomerID IN ( -- IN 在 SQL 中确实用于查找列值是否等于列表中的任意一个值
    SELECT CustomerID
    FROM Sales.Customer
    WHERE CountryRegion = 'Canada');

SELECT c.CustomerID, o.SalesOrderID
FROM Sales.Customer AS c
JOIN Sales.SalesOrderHeader AS o
    ON c.CustomerID = o.CustomerID
WHERE c.CountryRegion = 'Canada';

-- Employees 表：包含员工信息（EmployeeID, FirstName, LastName）。
-- SalesOrders 表：包含订单信息（OrderID, EmployeeID, OrderAmount）。
-- 如果我们使用嵌套查询来查找员工和他们的订单金额（假设我们只想得到销售订单中订单金额大于 1000 的员工信息），可以写如下查询：
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE EmployeeID IN (
    SELECT EmployeeID
    FROM SalesOrders
    WHERE OrderAmount > 1000
);
--  限制： 在这个查询中，外部查询只能返回 Employees 表的列。如果你需要包括来自 SalesOrders 表的列（比如 OrderAmount），就不能通过这个嵌套查询来直接获取了。

-- 如果你需要从多个表中获取信息，JOIN 更合适。你可以通过 JOIN 连接两个表，并选择你需要的列：
SELECT e.EmployeeID, e.FirstName, e.LastName, s.OrderAmount
FROM Employees e
JOIN SalesOrders s ON e.EmployeeID = s.EmployeeID
WHERE s.OrderAmount > 1000;
-- 我们通过 JOIN 连接了 Employees 表和 SalesOrders 表。
-- 外部查询返回了 Employees 表的列（EmployeeID, FirstName, LastName）和 SalesOrders 表的列（OrderAmount）。
-- 通过 WHERE 子句过滤订单金额大于 1000 的记录。

SELECT EmployeeID, Name
FROM Employees e
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.EmployeeID = e.EmployeeID
);

-- 这就是 关联子查询（Correlated Subquery），它会对 Employees 表中的每一行执行一次，检查该员工是否在 Orders 表中有对应的记录。

-- 由于子查询依赖于外部查询，它无法单独运行，因此无法直接测试子查询的结果。
-- 调试时，通常需要先单独运行 类似的非关联查询 来检查 Orders 表中是否有 EmployeeID，例如：

SELECT DISTINCT EmployeeID FROM Orders;

-- 然后再使用 EXISTS 语句进行联调。

SELECT SalesOrderID, CustomerID, OrderDate
FROM SalesLT.SalesOrderHeader AS o1
WHERE SalesOrderID =
    (SELECT MAX(SalesOrderID)
     FROM SalesLT.SalesOrderHeader AS o2
     WHERE o2.CustomerID = o1.CustomerID)
ORDER BY CustomerID, OrderDate;

SELECT CustomerID, CompanyName, EmailAddress 
FROM Sales.Customer AS c 
WHERE
(SELECT COUNT(*) 
  FROM Sales.SalesOrderHeader AS o
  WHERE o.CustomerID = c.CustomerID) > 0;

SELECT CustomerID, CompanyName, EmailAddress 
FROM Sales.Customer AS c 
WHERE EXISTS
(SELECT * 
  FROM Sales.SalesOrderHeader AS o
  WHERE o.CustomerID = c.CustomerID);

SELECT CustomerID, CompanyName, EmailAddress 
FROM SalesLT.Customer AS c 
WHERE NOT EXISTS
  (SELECT * 
   FROM SalesLT.SalesOrderHeader AS o
   WHERE o.CustomerID = c.CustomerID);