Question

用SQL统计每种商品的销售总额

统计2007年每种商品的销售总额，按销售总额降序显示结果
Products表--ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock
Orders表--OrderID CustomerID EmployeeID OrderDate
OrderDetails表--OrderID ProductID UnitPrice Quantity

select ProductName,sum(UnitPrice*Quantity)--这个销售总额不知怎写
from Products join OrderDetails
on Products.ProductID=OrderDetails.ProductID join Orders
on OrderDetails.OrderID=Orders.OrderID
where OrderDate>='2007-1-1'and OrderDate < '2007-12-1'
order by sum desc

Answer 1

你写的基本差不多了，不过表连接的顺序最好按用到的前后来连接，不然影响效率
select P.ProductName, sum(OD.UnitPrice*OD.Quantity) total_sales
from Orders O
join OrderDetails OD on OD.OrderID=O.OrderID
join Products P on P.ProductID=OD.ProductID

where OD.OrderDate>='2007-1-1'and OD.OrderDate < '2007-12-1'
group by P.ProductName
order by total_sales desc

Answer 2

select ProductName,sum(UnitPrice*Quantity)--这个销售总额不知怎写
from Products join OrderDetails
on Products.ProductID=OrderDetails.ProductID join Orders
on OrderDetails.OrderID=Orders.OrderID
where OrderDate>='2007-1-1'and OrderDate < '2007-12-1'
group by Products.ProductID,Products.ProductName

Answer 3

select a.productname,sum(uitprict*quantity)
from products a,orders b,orderdetails c
where a.productid=c.productid(+)
and b.orderId=c.orderid
and b.orderdate between to_date('20170101','yyyy-mm-dd') and to_date('20171231','yyyy-mm-dd')
group by a.productname
order by sum(uitprict*quantity) desc;

Answer 4

select ProductName,sum(UnitPrice*Quantity)

from Products a
join OrderDetails b

on a.ProductID=b.ProductID
join Orders c

on b.OrderID=c.OrderID

where c.OrderDate between'2007-01-01'and'2007-12-01'
group by ProductName
order by sum(UnitPrice*Quantity) desc

Answer 5

select ProductName,sum(UnitPrice*Quantity) as SumPrice
from Products join OrderDetails
on Products.ProductID=OrderDetails.ProductID join Orders
on OrderDetails.OrderID=Orders.OrderID
where OrderDate>='2007-1-1'and OrderDate < '2007-12-1' group by ProductName order by sum desc