mysql - Adventure Works 2014 SQL 查询

Question

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

我想显示所有客户以及每个客户有多少订单。

我尝试单独编写每个选择语句(见下文)，但我希望能够将两个查询合并为一个。

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