SQL 从不同来源选择

Question

我真的不知道我在用 SQL 做什么，但我有两个表。我知道如果列相等，您可以从一个表中提取数据并将其添加到另一个表中。 所以我想要这样的东西：

SELECT Sum(OrderDetails.Vendor_Price * OrderDetails.Quantity)
WHERE Orders.OrderID=OrderDetails.OrderID
AS COGS

基本上，订单和订单详细信息表是两个单独的表，但订单详细信息具有将其与订单表中的订单关联的 OrderID 元素。所以我的函数是 Orders.OrderID=OrderDetails.OrderID，即实现总和的位置，我希望每个订单都有它。 我找到了一个示例代码，它使用大量多余的代码来完成此任务：

SELECT orders.orderid,
       orders.cogs
FROM   (SELECT orders.orderid,
               orderdetails.cogs
        FROM   (SELECT orders.orderid AS orderid
                FROM   (((orders WITH(nolock)
                          LEFT JOIN paymentmethods WITH(nolock)
                            ON orders.paymentmethodid =
                               paymentmethods.paymentmethodid)
                         LEFT JOIN shippingmethods WITH(nolock)
                           ON orders.shippingmethodid =
                              shippingmethods.shippingmethodid)
                        LEFT JOIN customers WITH(nolock)
                          ON orders.customerid = customers.customerid)
                GROUP  BY orders.orderid) orders
               INNER JOIN (SELECT
                          orders.orderid
                          AS orderid,
                                  COUNT(orderdetails.orderdetailid)
                          AS
                                                            orderdetails_count
                                                            ,
                                  SUM(orderdetails.quantity)
                                                            AS quantity,
                                  SUM(orderdetails.vendor_price *
                          orderdetails.quantity) AS
                                                            cogs,
                                  CASE
                                    WHEN SUM(vendor_price) IS NULL THEN NULL
                                    ELSE SUM(( CASE
                                                 WHEN orderdetails.productcode
                                                      LIKE
                                                      'DSC-%'
                                               THEN
                                                 orderdetails.productprice
                                                 - Isnull(
                                                 orderdetails.vendor_price, 0)
                                                 ELSE orderdetails.productprice
                                                      -
                                                      orderdetails.vendor_price
                                               END ) * orderdetails.quantity)
                                  END
                          AS
                                                            profit,
                                  CASE SUM(orderdetails.productprice *
                          orderdetails.quantity)
                                    WHEN 0 THEN 0
                                    ELSE Round(( ( SUM(orderdetails.productprice
                                                       *
                                                       orderdetails.quantity)
                                                   - SUM(
                                                     orderdetails.vendor_price *
                                                     orderdetails.quantity) ) /
SUM(
             orderdetails.productprice
             *
             orderdetails.quantity) )
* 100,
1)
END
AS
           profitmargin
FROM   (((orders WITH(nolock)
LEFT JOIN paymentmethods WITH(nolock)
ON orders.paymentmethodid =
paymentmethods.paymentmethodid)
LEFT JOIN shippingmethods WITH(nolock)
ON orders.shippingmethodid =
shippingmethods.shippingmethodid)
LEFT JOIN customers WITH(nolock)
ON orders.customerid = customers.customerid)
LEFT JOIN orderdetails WITH(nolock)
ON orders.orderid = orderdetails.orderid
WHERE  orders.orderstatus <> 'Cancelled'
GROUP  BY orders.orderid) orderdetails
ON orders.orderid = orderdetails.orderid) orders
ORDER  BY orders.orderid DESC  

这基本上将订单及其每个订单的 COGS 传递到表中。但每次我尝试删除一行多余的代码时，都会收到错误。像 ShippingMethodID 这样的东西是不必要的。请帮忙。

编辑：

选择 Orders.OrderID、Orders.SalesRep_CustomerID、Orders.Total_Payment_Received、Orders.SalesTax1、SumDetails.COGS、ISNULL(Total_Shipping_Cost.Shipping_Cost,0) 作为 Shipping_Cost

FROM 订单

JOIN

(SELECT OrderID, Sum(OrderDetails.Vendor_Price * OrderDetails.Quantity)

作为销货成本

FROM OrderDetails

GROUP BY OrderID)

AS SumDetails

ON SumDetails.OrderID=Orders.OrderID LEFT

JOIN

(SELECT OrderID, SUM(Shipment_Cost)

AS Shipping_Cost

FROM Trackingnumbers

GROUP BY OrderID)

AS Total_Shipping_Cost

(SELECT CASE

WHEN Orders.ShippingMethodID

in (19, 20, 21) , 25, 26, 27, 28, 30, 31, 502)

THEN 5

ELSE 0

END)

AS ServiceCharge

ON Total_Shipping_Cost.OrderID = Orders.OrderID

WHERE Orders.OrderStatus = '已发货'

AND Orders.ShipDate > (GETDATE()-6)

AND Orders.PaymentAmount = Orders.Total_Payment_Received

Answer 1

这就是 JOIN 的用途

在您的示例中，这就是您要做的事情

SELECT  Orders.OrderId, SUM(OrderDetails.Vendor_Price * OrderDetails.Quantity)
FROM    Orders
        INNER JOIN OrderDetails
          ON Orders.OrderId = OrderDetails.OrderId
GROUP BY Orders.OrderId

您可以在网上阅读大量有关联接和聚合数据的文章

链接到解释联接的文章 http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

这是一篇关于 GROUP BY 的文章：< a href="http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server" rel="nofollow">http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server

Answer 2

这应该让您开始：

SELECT Orders.OrderID,
SumDetails.COGS
FROM Orders
JOIN 
    (SELECT OrderID,
    Sum(OrderDetails.Vendor_Price * OrderDetails.Quantity) AS COGS
    FROM OrderDetails
    GROUP BY OrderID) AS SumDetails
ON SumDetails.OrderID=Orders.OrderID

编辑：添加订单中的其他列（这就是我喜欢子查询方法的原因 - 它们不必位于 GROUP BY 中）：

SELECT Orders.OrderID,
Orders.SalesRep_CustomerID,
Orders.Total_Payment_Received,
Orders.S‌​alesTax1,
SumDetails.COGS,
ISNULL(Total_Shipping_Cost.Shipping_Cost,0) as Shipping_Cost
FROM Orders
JOIN 
    (SELECT OrderID,
    Sum(OrderDetails.Vendor_Price * OrderDetails.Quantity) AS COGS
    FROM OrderDetails
    GROUP BY OrderID) AS SumDetails
ON SumDetails.OrderID=Orders.OrderID
LEFT JOIN
    (SELECT OrderID,
    SUM(Shipment_Cost) AS Shipping_Cost 
    FROM Trackingnumbers
    GROUP BY OrderID) AS Total_Shipping_Cost 
ON Total_Shipping_Cost.OrderID = Orders.OrderID  
WHERE Orders.OrderStatus = 'Shipped' 
AND Orders.ShipDate > (GETDATE()-6)
AND Orders.PaymentAmount = Orders.Total_Payment_Received

Answer 3

为了回答你的问题，

SELECT Orders.OrderID,Sum(OrderDetails.Vendor_Price * OrderDetails.Quantity) AS COGS
FROM Orders
INNER JOIN OrderDetails ON Orders.OrderID=OrderDetails.OrderID
GROUP BY Orders.OrderID

我相信这相当于你所拥有的......我希望这会有所帮助，因为我认为你想要的可能没有考虑到当前正在发生的事情的逻辑。

SELECT
    orders.orderid AS orderid,
    COUNT(orderdetails.orderdetailid) AS orderdetails_count,
    SUM(orderdetails.quantity) AS quantity,
    SUM(orderdetails.vendor_price * orderdetails.quantity) AS cogs,
    CASE
        WHEN SUM(vendor_price) IS NULL THEN NULL
        ELSE SUM(
                    CASE
                        WHEN orderdetails.productcode LIKE 'DSC-%' THEN (orderdetails.productprice - Isnull(orderdetails.vendor_price, 0))* orderdetails.quantity
                        ELSE (orderdetails.productprice-orderdetails.vendor_price)* orderdetails.quantity
                    END
                )
    END AS profit,
    CASE SUM(orderdetails.productprice *orderdetails.quantity)
        WHEN 0 THEN 0
        ELSE Round(((SUM(orderdetails.productprice * orderdetails.quantity) - SUM(orderdetails.vendor_price * orderdetails.quantity)) / SUM(orderdetails.productprice * orderdetails.quantity)) * 100,1)
    END AS profitmargin
FROM orders WITH(nolock)
LEFT JOIN paymentmethods WITH(nolock) ON orders.paymentmethodid = paymentmethods.paymentmethodid
LEFT JOIN shippingmethods WITH(nolock) ON orders.shippingmethodid = shippingmethods.shippingmethodid
LEFT JOIN customers WITH(nolock) ON orders.customerid = customers.customerid
LEFT JOIN orderdetails WITH(nolock) ON orders.orderid = orderdetails.orderid
WHERE  orders.orderstatus <> 'Cancelled'
GROUP  BY orders.orderid
ORDER  BY orders.orderid DESC

Answer 4

您是否正在寻找这样的东西：

SELECT Orders.OrderID, Sum(OrderDetails.Vendor_Price * OrderDetails.Quantity)
FROM Orders
INNER JOIN OrderDetails on Orders.OrderID = OrderDetails.OrderID
WHERE ....
GROUP BY Orders.OrderID