SQL Server 中的派生表 [英] Derived table in SQL Server
本文介绍了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屋!
查看全文