数据库知识重温（1+）：挑战和解决方案

#挑战

（数据库为微软提供的示例，结构如下）

1、写出 orders表对应的 order details ，返回列如下：
OrderID, ProductName，UnitPrice, Quantity, Discount

2、写出orders表对应的销售数据
OrderID,合计销售额，合计销售数量

3、写出Products表每种产品的销售数据
ProductName，合计销售额，合计销售数量

合计销售金额要求计入折扣

#解决方案

 

select dbo.Orders.OrderID,ProductName ,dbo.[Order Details].UnitPrice, Quantity,Discount
from
    dbo.[Order Details]
        INNER JOIN
    dbo.Orders on dbo.Orders.orderID=dbo.[Order Details].OrderID
    INNER JOIN
    dbo.Products on dbo.Orders.orderID=dbo.[Order Details].OrderID
order by dbo.Orders.orderID;

select dbo.Orders.OrderID ,sum(Quantity*UnitPrice-Discount*UnitPrice) as "合计销售额",sum(Quantity) as "合计销售数量"
from
    dbo.[Order Details]
        INNER JOIN
    dbo.Orders 
    on dbo.Orders.orderID=dbo.[Order Details].OrderID
group by dbo.Orders.OrderID

select dbo.Products.ProductName ,sum(UnitsOnOrder*dbo.Products.UnitPrice) as "合计销售额",sum(UnitsOnOrder) as"合计销售数量"
from
    dbo.[Order Details]
        INNER JOIN
    dbo.Products
    on dbo.Products.ProductID=dbo.[Order Details].ProductID
group by dbo.Products.ProductName