当我运行对此问题的查询时，不获取任何值

Question

作业问题是..。

列出订单的客户名称、订单状态、订购日期、订单上的项目计数和订单上的平均订购数量(如果订单上的项目数量大于300 )。

我正在使用冒险工程2019年的任务。

我想出的答案是.

SELECT
    LastName + ', ' + FirstName AS 'Customer Name',
    ssoh.Status AS 'Order Status',
    ssoh.OrderDate AS 'Date Order',
    SUM(ssod.Orderqty) AS 'Count of Items',
    AVG(ssod.Orderqty) AS 'Average Quantity'
FROM
    Person.Person pp
    JOIN Sales.SalesOrderHeader ssoh ON pp.BusinessEntityID = ssoh.CustomerID
    JOIN Sales.SalesOrderDetail ssod on ssoh.SalesOrderID = ssod.SalesOrderid
GROUP BY
    LastName + ', ' + FirstName,
    ssoh.OrderDate,
    ssoh.Status
HAVING
    SUM(ssod.OrderQty) > 300

当我将内部联接更改为外部联接时，我会得到客户名称的空值。我加倍检查了"customerID“外键，以确保它与主键"BusinessEntityID”相同，并获得结果。任何想法都将不胜感激。谢谢

Answer 1

在快速查看在线模式(我没有安装它)的基础上，我的理解是，您需要通过Person.Person将Sales.Customer连接到Sales.SalesOrderHeader。因此，也许可以尝试以下几点：

FROM
    Person.Person pp
    JOIN Sales.Customer sc ON pp.BusinessEntityID= sc.PersonID
    JOIN Sales.SalesOrderHeader ssoh ON sc.CustomerID= ssoh.CustomerID
    JOIN Sales.SalesOrderDetail ssod on ssoh.SalesOrderID = ssod.SalesOrderid

Answer 2

首先，按LastName + ', ' + FirstName分组将提供不准确的结果。因为有大约380个名字和重复。示例：

​

​

其次，Manachi是正确的加入。

因此，您的最后查询应该如下所示：

WITH cte AS (
    SELECT
        BusinessEntityID AS CustomerId,
        LastName + ', ' + FirstName AS 'Customer Name',
        ssoh.Status AS 'Order Status',
        ssoh.OrderDate AS 'Date Order',
        SUM(ssod.Orderqty) AS 'Count of Items',
        AVG(ssod.Orderqty) AS 'Average Quantity'
    FROM
        Person.Person pp
        JOIN Sales.Customer c ON c.PersonID = pp.BusinessEntityID
        JOIN Sales.SalesOrderHeader ssoh ON c.CustomerID = ssoh.CustomerID
        JOIN Sales.SalesOrderDetail ssod on ssoh.SalesOrderID = ssod.SalesOrderid
    GROUP BY
        BusinessEntityID,
        LastName + ', ' + FirstName,
        ssoh.OrderDate,
        ssoh.Status
    HAVING
        SUM(ssod.OrderQty) > 300
)
SELECT
    [Customer Name],
    [Order Status],
    [Date Order],
    [Count of Items],
    [Average Quantity]
    FROM cte