Adventure 2014 SQL查询

Question

我正在使用Adventure 2014数据库编写一个SQL查询。

我想告诉所有的客户和每个客户有多少订单。

我尝试自己编写每个select语句(参见下面)，但我希望能够将这两个查询合并为一个。

select  s.CustomerID ,p.FirstName +' '+p.LastName as Name 
from Sales.Customer s,Person.Person p 
where s.CustomerID=p.BusinessEntityID
order by s.CustomerID


select CustomerID ,count(SalesOrderID) as OrdersCount
from Sales.SalesOrderHeader
group by CustomerID

Answer 1

 SELECT s.CustomerID ,
        p.FirstName +' '+p.LastName as Name,
        count(SalesOrderID)
 FROM Sales.Customer s
 JOIN Person.Person p 
   ON s.CustomerID=p.BusinessEntityID
 LEFT JOIN Sales.SalesOrderHeader so
   ON s.Customer_ID = so.Customer_ID  
 GROUP BY s.CustomerID ,
        p.FirstName +' '+p.LastName as Name 
 order by s.CustomerID

Answer 2

​

​

我给你加上了一些新的东西。无论如何，OVER(column_name分区)子句被用作GROUP BY的替换，因为您打印的列比GROUP BY子句所允许的列要多。

Answer 3

表Sales.Customer References:Person.Person (PersonID -> BusinessEntityID)

SELECT s.CustomerID 
        ,p.FirstName +' '+p.LastName as Name
        ,COUNT(so.SalesOrderDetailID)
 FROM Sales.Customer s
 JOIN Person.Person p 
   ON s.PersonID=p.BusinessEntityID
 LEFT JOIN Sales.SalesOrderHeader h 
   ON h.CustomerID = s.CustomerID
 LEFT JOIN Sales.SalesOrderDetail so
   ON so.SalesOrderID = h.SalesOrderID
 GROUP BY s.CustomerID ,
        p.FirstName +' '+p.LastName
 order by s.CustomerID