SQL Server中的派生表

小编典典

SQL Server中的派生表

sql

我有这两个查询。我不知道如何将它们组合在一起以创建派生表。我想将第二个查询用作主查询，并在主查询的FROM子句中使用第一个查询。

SELECT EmailAddress, Orders.OrderID, SUM(ItemPrice * Quantity) AS OrderTotal
FROM Customers  
 JOIN Orders ON Customers.CustomerID = Orders.CustomerID
 JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID
GROUP BY EmailAddress, Orders.OrderID;

SELECT EmailAddress, MAX(ItemPrice) as LargestOrder 
FROM Customers  
 JOIN Orders ON Customers.CustomerID = Orders.CustomerID
 JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID
GROUP BY EmailAddress

阅读 245

2021-04-07

共1个答案

小编典典

您是否只想将结果放在同一张表中？只需执行以下操作：

SELECT FirstTable.*, SecondTable.LargestOrder
FROM (
  SELECT EmailAddress, Orders.OrderID, SUM(ItemPrice * Quantity) AS OrderTotal
  FROM Customers  
  JOIN Orders ON Customers.CustomerID = Orders.CustomerID
  JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID
  GROUP BY EmailAddress, Orders.OrderID) as FirstTable
JOIN (
  SELECT EmailAddress, MAX(ItemPrice) as LargestOrder 
  FROM Customers  
  JOIN Orders ON Customers.CustomerID = Orders.CustomerID
  JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID
  GROUP BY EmailAddress) as OtherTable ON FirstTable.EmailAddress = OtherTable.EmailAddress

2021-04-07