SQL Server 中的派生表 [英] Derived table in SQL Server

问题描述

我有这两个疑问.我不知道如何将它们组合在一起以制作派生表.我假设使用第二个查询作为主查询，并在主查询的 FROM 子句中使用第一个查询.

I have these two queries. I have no idea how to combine them together to make a derived table. I'm suppose to use the second query as the main query and use the first query in the FROM clause of the main query.

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

推荐答案

您是否只想将结果放在同一个表中?只需这样做:

Are you just wanting the results to be in the same table? Just do this:

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

这篇关于SQL Server 中的派生表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！