更复杂的查询

╰半橙微兮° 2022-09-23 14:00 164阅读 0赞

子查询：

将一个查询分解为一系列的步骤。

提供一个列表作为WHERE子句和\[IN|EXISTS|ANY|ALL\]的目标

为父查询中的每一个记录提供一个查询表

关联子查询：

外部查询获得一个记录，然后将该记录传递到内部查询。

内部查询根据传递的值执行。

然后内部查询将结果值传回外部查询，而外部查询利用这些值完成处理过程。

在WHERE子句中的关联子查询：

使用临时表：

SELECT soh.CustomerID, MIN(soh.OrderDate) AS OrderDate
    INTO #MinOrderDates
    FROM Sales.SalesOrderHeader soh
    GROUP BY soh.CustomerID;
    
    -- Do something additional with that information
    SELECT soh.CustomerID, soh.SalesOrderID, soh.OrderDate
    FROM Sales.SalesOrderHeader soh
    JOIN #MinOrderDates t
      ON soh.CustomerID = t.CustomerID
      AND soh.OrderDate = t.OrderDate
    ORDER BY soh.CustomerID;
    
    DROP TABLE #MinOrderDates;

使用关联子查询：

SELECT soh1.CustomerID, soh1.SalesOrderID, soh1.OrderDate
    FROM Sales.SalesOrderHeader soh1
    WHERE soh1.OrderDate = (SELECT Min(soh2.OrderDate) 
                          FROM Sales.SalesOrderHeader soh2
                          WHERE soh2.CustomerID = soh1.CustomerID)
    ORDER BY CustomerID;

在SELECT列表中的关联子查询：

SELECT sc.AccountNumber, 
       (SELECT Min(OrderDate) 
               FROM Sales.SalesOrderHeader soh
               WHERE soh.CustomerID = sc.CustomerID) 
               AS OrderDate
    FROM Sales.Customer sc;

处理NULL数据——ISNULL函数：

SELECT sc.AccountNumber,
    ISNULL(CAST((SELECT MIN(OrderDate)
     FROM Sales.SalesOrderHeader soh 
     WHERE soh.CustomerID=sc.CustomerID) AS varchar),'NEVER ORDERED')
      AS OrderDate 
      FROM Sales.Customer sc;

注：必须使用CAST()来完成这个工作。原因是要处理强制转换和饮食转换。

派生表：

创建派生表：

将生成结果集的查询用小括号括起来。

给查询结果取别名，这样他可以作为表被引用。

例：

SELECT DISTINCT sc.AccountNumber, sst.Name
    FROM Sales.Customer AS sc
    JOIN Sales.SalesTerritory sst
     ON sc.TerritoryID = sst.TerritoryID
    JOIN
      (SELECT CustomerID 
     FROM Sales.SalesOrderHeader soh
     JOIN Sales.SalesOrderDetail sod
            ON soh.SalesOrderID = sod.SalesOrderID
     JOIN Production.Product pp
            ON sod.ProductID = pp.ProductID
     WHERE pp.Name = 'HL Mountain Rear Wheel') AS dt1
      ON sc.CustomerID = dt1.CustomerID
    JOIN
      (SELECT CustomerID 
     FROM Sales.SalesOrderHeader soh
     JOIN Sales.SalesOrderDetail sod
            ON soh.SalesOrderID = sod.SalesOrderID
     JOIN Production.Product pp
            ON sod.ProductID = pp.ProductID
     WHERE Name = 'HL Mountain Front Wheel') AS dt2
      ON sc.CustomerID = dt2.CustomerID;

使用通用表达式（CTE）：

使用ETC：

使用多个ETC：

递归ETC：

使用EXISTS运算符：

使用EXISTS筛选数据：

SELECT BusinessEntityID, LastName + ', ' + FirstName AS Name
    FROM Person.Person pp
    WHERE EXISTS
       (SELECT BusinessEntityID 
               FROM HumanResources.Employee hre
               WHERE hre.BusinessEntityID = pp.BusinessEntityID);

混合数据类型：CAST和CONVERT

CAST是ANSI兼容的，而CONVERT不兼容。

CAST例：

SELECT 'The Customer has an Order numbered ' + CAST(SalesOrderID AS varchar)
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = 29825;

SELECT ColTS AS Uncoverted, CAST(ColTS AS int) AS Converted
    FROM ConvertTest;
    
    SELECT OrderDate, CAST(OrderDate AS varchar) AS Converted
    FROM Sales.SalesOrderHeader
    WHERE SalesOrderID = 43663;

CONVERT例：

SELECT OrderDate, CONVERT(varchar(12), OrderDate, 111) AS Converted
    FROM Sales.SalesOrderHeader
    WHERE SalesOrderID = 43663;
    
    SELECT OrderDate, CONVERT(varchar(12), OrderDate, 5) AS Converted
    FROM Sales.SalesOrderHeader
    WHERE SalesOrderID = 43663;

注：111给出日本标准，带4位数字的年份；5是意大利标准，带俩位数字年份。超过100的代表4位数字年份；小于100代表两位数字年份。