当数据中不存在时，SQL Server 动态数据透视返回空值

Question

我有 3 个表（tblPreference、tblCustomer、tblCustomerPreference），如下所示：

tblPreference:
ID       | Name            | DefaultValue
(int PK) | (nvarchar(100)) | (nvarchar(100))
-------------------------------
1        | Preference1     | 1
2        | Preference2     | Yes
3        | Preference3     | 1

tblCustomer:
CustomerID | ...
(int PK)
--------------------
1          | ...
2          | ...
3          | ...

tblCustomerPreference:
ID       | CustomerID | PreferenceID | Value
(int PK) | (int)      | (int)        | (nvarchar(100))
-------------------------------------------------------
1        | 1          | 1            | 0
2        | 1          | 2            | Yes
3        | 2          | 1            | 0
4        | 2          | 2            | No

我正在创建此数据的数据透视表，因此它是使用以下存储过程将所有内容都放在一行中，以便它始终会拉回所有首选项，如果它找到客户特定值，它将返回该值，否则它将返回默认值：

CREATE PROCEDURE [dbo].[usp_GetCustomerPreferences] @CustomerID int AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @PivotColumns nvarchar(max)
    DECLARE @PivotColumnsSelectable nvarchar(max)
    SELECT @PivotColumns = COALESCE(@PivotColumns + ',','') + QUOTENAME(Preference.Name),
           @PivotColumnsSelectable = COALESCE(@PivotColumnsSelectable + ',' + Char(10),'') + Preference.Source + '.' + QUOTENAME(Preference.Name) + ' AS ' + QUOTENAME(Preference.Name)
    FROM (SELECT [Name],
                 'PreferencePivot' AS [Source]
          FROM [dbo].[tblPreference]) Preference

    DECLARE @sqlText nvarchar(max)
    SELECT @sqlText = 'SELECT ' + @PivotColumnsSelectable + '
    FROM (SELECT tblPreference.Name AS PreferenceName,
                CASE
                    WHEN tblCustomerPreference.Value IS NOT NULL THEN tblCustomerPreference.Value
                    ELSE tblPreference.DefaultValue
                END AS Value,
                @innerCustomerID AS CustomerID
            FROM tblCustomerPreference
                RIGHT JOIN tblPreference ON tblCustomerPreference.PreferenceID = tblPreference.ID
            WHERE (tblCustomerPreference.CustomerID = @innerCustomerID OR tblCustomerPreference.ID IS NULL)) data
            PIVOT (MAX(Value)
                   FOR PreferenceName IN (' + @PivotColumns + ')) PreferencePivot'

    EXECUTE sp_executesql @sqlText, N'@innerCustomerID int', @CustomerID
END

我遇到的问题是当我查询时对于 CustomerID 1 或 2，一切都会按预期返回，所有值均按预期填充。但如果我查询 CustomerID 3，它将为其他客户填充的任何 PreferenceID 返回 NULL。如果我在没有 PIVOT 表达式的情况下运行查询，它将返回按预期填充的所有首选项。只有当我对数据进行 PIVOT 时，NULL 才会出现。我希望我错过了一些简单的事情，但我没有看到错误。

Answer 1

您在 CustomerID 的 1 和 2 中看到偏好 3 默认值的唯一原因是因为偏好 3 没有 tblCustomerPreference 记录，而不是因为 CustomerID=1 / 的组合不存在 tblCustomerPreference 记录偏好 3 和客户 ID=2 / 偏好 3。

在 RIGHT JOIN 条件中，您指定仅在首选项值上连接 tblCustomerPreference 和 tblPreference - 这只会实现 tblPreference 中没有 NO 匹配 ANY tblCustomerPreference 中的 customerID。如果您在 customerID = @innerCustomerID 上为该子句添加附加连接条件，您现在将执行您正在查找的操作：即给我所有首选项记录和任何匹配 tblCustomerPreference for CustomerID=@innerCustomerID。

不再获得 Customer 3 的结果。

通过简单地添加 CustomerID 1 和 Preference3 的 tblCustomerPreference 记录来尝试一下，您会注意到，您不仅会开始看到 Customer2 的 Prefernce3 值为 NULL，而且甚至 就像这就是您在 WHERE 子句中尝试执行的操作，但由于在查询处理期间 JOIN 在 WHERE 子句之前处理（遵循语句处理的正确逻辑顺序），因此您将获得严格基于首选项的中间结果集组合而不是客户和偏好组合。

所以，做一些小改变就可以了。基本上，只需向指定特定客户的 RIGHT JOIN 子句添加一个附加条件（即 @innerCustomerID）并删除整个 WHERE 子句即可。请注意，这还会产生副作用，即实际返回任何传递的 @CustomerID 的所有默认值，这些值甚至不作为客户存在 - 如果您想更改它以不为不存在的客户返回任何内容，只需添加一个检查在查询之前或包含一个 whereexists() 过滤器：

alter PROCEDURE [dbo].[usp_GetCustomerPreferences] @CustomerID int AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @PivotColumns nvarchar(max)
    DECLARE @PivotColumnsSelectable nvarchar(max)
    SELECT @PivotColumns = COALESCE(@PivotColumns + ',','') + QUOTENAME(Preference.Name),
           @PivotColumnsSelectable = COALESCE(@PivotColumnsSelectable + ',' + Char(10),'') + Preference.Source + '.' + QUOTENAME(Preference.Name) + ' AS ' + QUOTENAME(Preference.Name)
    FROM (SELECT [Name],
                 'PreferencePivot' AS [Source]
          FROM [dbo].[tblPreference]) Preference

    DECLARE @sqlText nvarchar(max)
    SELECT @sqlText = 'SELECT ' + @PivotColumnsSelectable + '
    FROM (SELECT tblPreference.Name AS PreferenceName,
                CASE
                    WHEN tblCustomerPreference.Value IS NOT NULL THEN tblCustomerPreference.Value
                    ELSE tblPreference.DefaultValue
                END AS Value,
                @innerCustomerID AS CustomerID
            FROM tblCustomerPreference
                RIGHT JOIN tblPreference 
                ON tblCustomerPreference.PreferenceID = tblPreference.ID
                AND tblCustomerPreference.CustomerID = @innerCustomerID
            ) data
            PIVOT (MAX(Value)
                   FOR PreferenceName IN (' + @PivotColumns + ')) PreferencePivot'

 print @sqlText

    EXECUTE sp_executesql @sqlText, N'@innerCustomerID int', @CustomerID
END