1)在名为“确定各客户按总销售额降序的排名”的表中生成一个按照三年内总销售额从大到小的排名的客户公司总销售额列表。
Select sum(UnitPrice*Quantity) 总销售额 from [Order Details] a,[Orders] b
where b.OrderDate > ((select distinct CONVERT(varchar(100), GETDATE()-(365*13), 23) from Orders)) group by ProductID order by 总销售额 asc 2)利用Northwind数据库中的数据,分析Northwind前十大客户最喜爱的前十种产品.select top 10 sum(a.Quantity) [sum],a.ProductID from [Order Details] a,[Orders] b,[Customers] c
where b.OrderID=a.OrderID and c.CustomerID=b.CustomerID
GROUP BY a.ProductID,b.CustomerID order by sum(a.Quantity) desc
不知道对不对,先放上去再说吧,大致意思差不多就行了
Select sum(UnitPrice*Quantity) 总销售额 from [Order Details] a,[Orders] b
where b.OrderDate > ((select distinct CONVERT(varchar(100), GETDATE()-(365*13), 23) from Orders)) group by ProductID order by 总销售额 asc 2)利用Northwind数据库中的数据,分析Northwind前十大客户最喜爱的前十种产品.select top 10 sum(a.Quantity) [sum],a.ProductID from [Order Details] a,[Orders] b,[Customers] c
where b.OrderID=a.OrderID and c.CustomerID=b.CustomerID
GROUP BY a.ProductID,b.CustomerID order by sum(a.Quantity) desc
不知道对不对,先放上去再说吧,大致意思差不多就行了
Select CustomerID 宫户名称,sum(UnitPrice*Quantity) 总销售额
from [Order Details] a,[Orders] b
where b.OrderID = b.OrderID
and b.OrderDate between dateadd(day,-365*3,(select max(orderdate) from Orders)) and (select max(orderdate) from Orders)
group by CustomerID
order by 总销售额 desc
select top 10 b.CustomerID ,sum(a.Quantity) [sum],a.ProductID
from [Order Details] a,[Orders] b,[Customers] c
where b.OrderID=a.OrderID and c.CustomerID=b.CustomerID
GROUP BY a.ProductID,b.CustomerID
order by sum(a.Quantity) desc