65.9K
CodeProject 正在变化。 阅读更多。
Home

创建 JOIN 查询的简单方法

Emad Al-Ashi
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.54/5 (7投票s)

2006 年 4 月 26 日

CPOL

5分钟阅读

viewsIcon

44502

一种简单的图形化方法来创建复杂的嵌套 SQL Join 查询。

目录

引言

构建 SQL 查询一直被认为是开发人员的噩梦；有那么多嵌套查询和不同的 Join。在这篇文章中，我试图说明我用来构建嵌套 Join 查询的一种较为图形化且简单的方法，因为它们是开发人员最常遇到的查询类型。它解决了我在 Join 查询方面的问题，希望也能解决你的问题。

图示

解释此问题最简单的方法是举例说明，所以我选择了 Microsoft SQL Server 随附的非常著名的 Northwind 数据库。稍后，我们将看到：我们想要达到的示例结果，Northwind 数据库中的“按国家/地区划分的员工销售额”存储过程如何实现它，以及我们的查询将如何实现它。

所需的查询结果

所需结果可以用许多人类表达的语句来描述。您会发现自己可以提出自己的表述，但为了缩小范围，我选择以下表述：

“显示所有订单，这些订单是在StartDate和EndDate（日期是参数）之间发货的，显示OrderId、其Shipping Date、其Subtotal、（下订单的）员工的First Name、Last Name和Country”。

这里展示了结果的样本

要获得此类结果的查询有点复杂；这绝不应该吓到你，让你停止阅读本文的其余部分。这里的整个问题是如何使复杂语句易于构建，所以当你看到这些语句时，屏住呼吸，忍耐一下。

Northwind 的查询

Northwind 数据库有一个现成的存储过程可以实现示例结果，名为“按国家/地区划分的员工销售额”；我在这里包含它的目的是将其与我们的查询进行比较；在复杂性和性能方面。

create procedure "Employee Sales by Country" 
       @Beginning_Date DateTime, 
       @Ending_Date DateTime 
AS
SELECT 
       Employees.Country, 
       Employees.LastName, 
       Employees.FirstName, 
       Orders.ShippedDate, 
       Orders.OrderID, 
       "Order Subtotals".Subtotal AS SaleAmount

FROM Employees 
INNER JOIN 
       (Orders 
       INNER JOIN 
       "Order Subtotals" 
       ON Orders.OrderID = "Order Subtotals".OrderID
       ) 
    
ON Employees.EmployeeID = Orders.EmployeeID

WHERE 
Orders.ShippedDate Between @Beginning_Date And @Ending_Date

GO

但是，如果你注意到，这个查询已经使用了视图“Order Subtotals”，这是一个现成的语句。为了展示查询的实际样子，让我们用其真实查询替换“Order Subtotals”视图。以下是结果

SELECT 
       Employees.Country, 
       Employees.LastName, 
       Employees.FirstName, 
       Orders.ShippedDate, 
       Orders.OrderID, 
       OrderSubtotals.Subtotal AS SaleAmount

FROM Employees INNER JOIN 
       (Orders INNER JOIN (

       SELECT 
              "Order Details".OrderID, 
              Sum(CONVERT(money,("Order Details".UnitPrice*
                                  Quantity*(1-Discount)/100))*100) AS Subtotal
       FROM "Order Details"
       GROUP BY "Order Details".OrderID

       ) OrderSubtotals ON Orders.OrderID = OrderSubtotals.OrderID) 
    ON Employees.EmployeeID = Orders.EmployeeID

WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

在查询分析器中执行上述代码将生成预期的结果（您只需要将参数替换为适当的值）。

我们的查询

现在，轮到我们创建查询了，目标是获得相同的结果。所以，让我们按照步骤进行，看看我们是否会成功。

第一步：游戏中涉及的表

首先，我们指定需要哪些表（以及它们的必需列）才能得出所需的结果（我们不应忘记获取所需的作为主键的字段和作为外键的字段，通过这些字段我们将连接到其他表）。我认为这很容易，而且很直接。

  • 订单:
    • OrderId (PK)
    • EmployeeId (FK，用于连接到 Employees)
    • ShippedDate
  • OrderDetails
    • OrderId (FK，用于连接到 Orders)
    • 单价
    • 数量
    • 折扣
    • SubTotal（通过 UnitPrice、Quantity 和 Discount 列计算出的结果列）
  • 员工
    • EmployeeId (PK)
    • FirstName
    • LastName
    • 国家

第二步：绘制图

确定了所有必需的表及其列之后，我们绘制一个查询图，显示到最终结果的路径；描述它的最好方法是将其想象成一群溪流在某个点汇合，直到它们最终形成一条大河。

我们从最核心的查询（对其他查询的依赖最少）开始，然后在其基础上构建，直到获得最终结果。该图由块（查询）和线（用于形成 Join）组成。

我首先想到的核心查询是 Orders 的 Subtotals 查询；这个查询只包含一个表；它对其中的一些字段进行计算，添加一个别名为“Subtotal”的新列。

你可能已经注意到，我给查询标记为 Q1，因为我们将继续在此基础上构建（添加溪流）。只有第一个查询（Q1）以及所有 Join 的结果查询才会被命名；其他任何内部查询（第一个查询除外）都不会被命名。

现在，我们需要将最接近它的查询追加到此查询中；对我来说，我会通过 OrderId 字段将其与订单信息（Shipped Date 和下订单的 Employee Id）连接起来，这类似于

结果

现在一切都很好，剩下的不多了；我们只需要将此与员工信息（First Name、Last Name 和 Country）通过 EmployeeID 连接起来，这类似于

结果

至此，我们完成了完整的查询图，并准备开始创建查询。

第三步：创建查询

现在，对于每个 Join，我们都应用以下简单规则，直到得到最终结果（参考图表了解什么是内部查询，什么是 Qx）

Qx+1=

SELECT 
       intrinsic.fields, 
       Qx.fields 
FROM intrinsic 
JOIN
       ( body of Qx ) as Qx 

ON Qx.[FK] = intrinsic.[PK]

在这里，我们开始应用上述规则

Q1（基础查询）=

SELECT 
       "Order Details".OrderID, 
       Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*
                         (1-Discount)/100))*100) AS Subtotal

FROM "Order Details"
GROUP BY "Order Details".OrderID

Q2 =

SELECT 
       Orders.OrderID OrderId, 
       Orders.EmployeeID, 
       Orders.ShippedDate, 
       Q1.Subtotal
FROM
    Orders
JOIN
(
  SELECT "Order Details".OrderID, Sum(CONVERT(money, 
    ("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS Subtotal
  FROM "Order Details"
  GROUP BY "Order Details".OrderID

) AS  Q1 ON Orders.OrderId = Q1.OrderID

现在，我们对最终结果也做同样的事情。

Q3（最终）=

SELECT 
   Employees.EmployeeID, 
   Employees.Country, 
   Employees.LastName, 
   Employees.FirstName, 
   Q2.OrderId, 
   Q2.ShippedDate, 
   Q2.Subtotal

FROM 
   Employees
JOIN 
(
   SELECT 
          Orders.OrderID OrderId, 
          Orders.EmployeeID, 
          Orders.ShippedDate, 
          Q1.Subtotal 
   FROM 
          Orders 
    JOIN( 

          SELECT 
                 "Order Details".OrderID, 
                 Sum(CONVERT(money,("Order Details".UnitPrice*
                             Quantity*(1-Discount)/100))*100) AS Subtotal
          FROM 
                 "Order Details"
          GROUP BY "Order Details".OrderID

   ) Q1 ON Orders.OrderId = Q1.OrderID

) AS Q2 ON Employees.EmployeeId = Q2.EmployeeId
WHERE Q2.ShippedDate BETWEEN '1/1/1900' AND '1/1/2006'

到此为止，我们完成了；查询已完成。

比较

但是，为了最后总结一下；将我们得出的查询与 Northwind 的“按国家/地区划分的员工销售额”存储过程进行比较，我们会注意到

  1. 复杂性：我们的查询中有更多的“SELECT”行；确实需要写更多内容，但更有条理。
  2. 性能：我在查询分析器中对两者进行了快速比较，两者的执行计划如下：

Northwinds SP“按国家/地区划分的员工销售额”

我们的查询

显示了与执行计划完全匹配。

结论

在本文中，我们已经看到如何在三个简单的步骤中构建下一个 Join 查询

  1. 指定涉及的表及其列
  2. 绘制图
  3. 创建查询

这可以进一步改进，并且通过更多的思考……活跃的开发人员可以创建一个自动化此过程的软件，使开发人员的生活更轻松。

© . All rights reserved.