代码之家  ›  专栏  ›  技术社区  ›  Marko

从两个不同的表中计算(数量*价格)的总和

  •  8
  • Marko  · 技术社区  · 14 年前

    我有两张桌子如下

    PRODUCT 桌子

    Id | Name | Price
    

    ORDERITEM 桌子

    Id | OrderId | ProductId | Quantity
    

    我在尝试这样的事情

    SELECT Id, SUM(Quantity * (select Price from Product where Id = Id)) as qty
    FROM OrderItem o
    WHERE OrderId = @OrderId
    

    但这当然行不通:)

    编辑: 我只想显示整个订单的总计,所以基本上是OrderItem中每行的数量*价格之和。下面是一些示例数据。

    表产品

    Id     Name            Price  
    1      Tomatoes        20.09    
    4      Cucumbers       27.72    
    5      Oranges         21.13    
    6      Lemons          20.05
    7      Apples          12.05
    

    Id         OrderId        ProductId        Quantity
    151        883            1                22
    152        883            4                11
    153        883            5                8
    154        883            6                62
    

    5 回复  |  直到 11 年前
        1
  •  20
  •   OMG Ponies    14 年前

    用途:

      SELECT oi.orderid,
             SUM(oi.quantity * p.price) AS grand_total,
        FROM ORDERITEM oi
        JOIN PRODUCT p ON p.id = oi.productid
       WHERE oi.orderid = @OrderId
    GROUP BY oi.orderid
    

    oi.quantity p.price 如果为null,则总和将返回null。

        2
  •  1
  •   Randy    14 年前

    我认为这个-包括空值=0

     SELECT oi.id, 
             SUM(nvl(oi.quantity,0) * nvl(p.price,0)) AS total_qty 
        FROM ORDERITEM oi 
        JOIN PRODUCT p ON p.id = oi.productid 
       WHERE oi.orderid = @OrderId 
    GROUP BY oi.id 
    
        3
  •  1
  •   Matt M    14 年前

    我想这正是你想要的。似乎您希望查看订单ID、订单中每个项目的小计以及订单的总金额。

    select o1.orderID, o1.subtotal, sum(o2.UnitPrice * o2.Quantity) as order_total from
    (
        select o.orderID, o.price * o.qty as subtotal
        from product p inner join orderitem o on p.ProductID= o.productID
        where o.orderID = @OrderId
    )as o1
    inner join orderitem o2 on o1.OrderID = o2.OrderID
    group by o1.orderID, o1.subtotal
    
        4
  •  0
  •   Beth    14 年前
    select orderID, sum(subtotal) as order_total from
    (
        select orderID, productID, price, qty, price * qty as subtotal
        from product p inner join orderitem o on p.id = o.productID
        where o.orderID = @orderID
    ) t
    group by orderID
    
        5
  •  0
  •   Alican Dönmez    11 年前

    我和马尔科有同样的问题,遇到了这样的解决方案:

    /*Create a Table*/
    CREATE TABLE tableGrandTotal
    (
    columnGrandtotal int
    )
    
    /*Create a Stored Procedure*/
    CREATE PROCEDURE GetGrandTotal
    AS
    
    /*Delete the 'tableGrandTotal' table for another usage of the stored procedure*/
    DROP TABLE tableGrandTotal
    
    /*Create a new Table which will include just one column*/
    CREATE TABLE tableGrandTotal
    (
    columnGrandtotal int
    )
    
    /*Insert the query which returns subtotal for each orderitem row into tableGrandTotal*/
    INSERT INTO tableGrandTotal
        SELECT oi.Quantity * p.Price AS columnGrandTotal
            FROM OrderItem oi
            JOIN Product p ON oi.Id = p.Id
    
    /*And return the sum of columnGrandTotal from the newly created table*/    
    SELECT SUM(columnGrandTotal) as [Grand Total]
        FROM tableGrandTotal
    

    EXEC GetGrandTotal