联接表中的总和字段 [英] Total sum fields from joined tables

问题描述

查询订单表：

SELECT O.ID, C.SecondName, E.SecondName, O.DateOf , O.ClientID, O.EmployeeID, O.Desc
FROM ((Client AS C INNER JOIN [ORDER] AS O ON C.ID = O.ClientID) 
    INNER JOIN Employee AS E ON E.ID = O.EmployeeID) 

查询订单项表：

SELECT  OrderItem.ID, 
        ProductID, 
        OrderID, 
        Quantity, 
        P.Title,
        P.CurrentPrice, 
        P.ID, 
        (P.CurrentPrice* OrderItem.Quantity) AS Total
FROM 
    OrderItem 
INNER JOIN 
    Product AS P 
ON 
    OrderItem.ProductID = P.ID
GROUP BY 
    OrderID,
    OrderItem.ID, 
    ProductID, 
    Quantity, 
    P.Title,
    P.CurrentPrice, 
    P.ID

总订单价格：

select OrderID, sum(Total)
from (
SELECT 
    OrderItem.ID
    , ProductID
    , OrderID
    , Quantity
    , P.Title
    ,P.CurrentPrice
    , P.ID
    , (P.CurrentPrice* OrderItem.Quantity) AS Total
FROM OrderItem 
INNER JOIN Product AS P ON OrderItem.ProductID = P.ID
) t 

group by OrderId

我如何加入表以获取订单表的总订单价格字段？

How i can joined tables for get total order price field for Order's table?

推荐答案

1）第三个查询（称为总订单价格）具有不必要的子查询和字段。等效项如下：

1) The third query, what you call "total order price", has an unnecessary subquery and fields. Here's the equivalent:

SELECT 
    OrderID, 
    Sum(P.CurrentPrice* OrderItem.Quantity) AS OrderTotal
FROM OrderItem INNER JOIN Product AS P ON OrderItem.ProductID = P.ID
GROUP BY OrderId

2）我将您对订单表的订单价格字段的请求解释为您要获取订单详细信息以及订单价格。（？）您已经展示了正确的多表联接您的第一个查询。只需遵循相同的模式即可加入[Order]表...以及（可选）客户和/或雇员表。

2) I interpret your request for "order price field for Order's table" to mean that you want to get order details along with the order price.(?) You have already demonstrated the proper multi-table joins in your first query. Just follow that same pattern to join in the [Order] table... and optionally the client and/or employee tables.

仅订购表：

SELECT  
    O.ID, O.DateOf , O.ClientID, O.EmployeeID, O.Desc,
    Sum(P.CurrentPrice* OrderItem.Quantity) AS [OrderTotal]
FROM
    ([Order] As O INNER JOIN OrderItem ON O.ID = OrderItem.OrderID)
    INNER JOIN Product AS P ON OrderItem.ProductID = P.ID
GROUP BY 
    O.ID, O.DateOf , O.ClientID, O.EmployeeID, O.Desc

关键思想是任何未在您的 sum（）或其他聚合函数必须在Group By子句中。

The key idea is that any selected field that is not in your sum() or other aggregate function must be in the Group By clause.

SELECT  
    O.ID, C.SecondName, E.SecondName, 
    O.DateOf , O.ClientID, O.EmployeeID, O.Desc,
    Sum(P.CurrentPrice* OrderItem.Quantity) AS [OrderTotal]
FROM ((
    (Client AS C INNER JOIN [ORDER] AS O ON C.ID = O.ClientID)
    INNER JOIN Employee AS E ON E.ID = O.EmployeeID)
    INNER JOIN OrderItem ON O.ID = OrderItem.OrderID)
    INNER JOIN Product AS P ON OrderItem.ProductID = P.ID
GROUP BY 
    O.ID, C.SecondName, E.SecondName, O.DateOf,
    O.ClientID, O.EmployeeID, O.Desc

这篇关于联接表中的总和字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！