将 SUM 组合插入到与其他列 SQL 的选择中

Question

我在sql中的sum函数有问题，就像这样：

SELECT Member.Name, Book.Title AS BookBought, Order_Member_Det.Quantity, Order_Member_Det.Price, Order_Member.Total
FROM Order_Member
INNER JOIN OrderWithMember ON Order_Member.OrderID = OrderWithMember.OrderID
INNER JOIN Order_Member_Det ON OrderWithMember.MemberOrderID = Order_Member_Det.MemberOrderID
INNER JOIN Member ON OrderWithMember.MemberID = Member.MemberID
INNER JOIN Book ON Order_Member_Det.BookID = Book.BookID

结果是：

Name    BookBought           Quantity Price Total
----------------------------------------------------
John    Flip it                 2     50    150
John    California Penal Code   1     50    150
Jack    Forum Internationale    2     80    160
Alice   Securitization          2     80    210
Alice   The Cervical Spine      1     50    210
Kevin   Trading Commodities     1     55    55
James   Aerodynamics            1     90    90

我想通过使用 SUM(总计( 在表中添加所有总计的总和，如下所示：

SELECT Member.Name, Book.Title AS BookBought, Order_Member_Det.Quantity, Order_Member_Det.Price, Order_Member.Total, **SUM(Order_Member.Total)**
FROM Order_Member
INNER JOIN OrderWithMember ON Order_Member.OrderID = OrderWithMember.OrderID
INNER JOIN Order_Member_Det ON OrderWithMember.MemberOrderID = Order_Member_Det.MemberOrderID
INNER JOIN Member ON OrderWithMember.MemberID = Member.MemberID
INNER JOIN Book ON Order_Member_Det.BookID = Book.BookID

但是后来我得到一个错误：

Msg 8120，级别 16，状态 1，第 264
行 列 'Member.Name' 在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中。

但是如果我像这样用这样的总和创建新的选择，它可以工作：

SELECT SUM(Total) From Order_Member;

它可以生成此输出：

ColumnName
665

那么我如何在给定的情况下组合两个选择，我也使用了 INNER JOIN，所以子查询在这里可能不起作用。有什么想法吗？

Answer 1

这也应该有效(未经测试(

with cte as
(
    SELECT Member.Name, Book.Title AS BookBought, 
        Order_Member_Det.Quantity, Order_Member_Det.Price, Order_Member.Total
    FROM Order_Member
    INNER JOIN OrderWithMember ON Order_Member.OrderID = OrderWithMember.OrderID
    INNER JOIN Order_Member_Det ON OrderWithMember.MemberOrderID = Order_Member_Det.MemberOrderID
    INNER JOIN Member ON OrderWithMember.MemberID = Member.MemberID
    INNER JOIN Book ON Order_Member_Det.BookID = Book.BookID
)
select *, (select sum(total) from cte) as SumTotal
from cte

Answer 2

在 SQL Server 中，您可以使用窗口函数执行此操作：

SELECT m.Name, b.Title AS BookBought, omd.Quantity, omd.Price, om.Total,
       SUM(Order_Member.Total) OVER (PARTITION BY m.MemberId) as MemberTotal
FROM Order_Member om INNER JOIN
     OrderWithMember owm
     ON om.OrderID = owm.OrderID INNER JOIN
     Order_Member_Det omd
     ON owm.MemberOrderID = omd.MemberOrderID INNER JOIN
     Member m
     ON owm.MemberID = m.MemberID INNER JOIN
     Book b
     ON omd.BookID = b.BookID;