代码之家 › 专栏 › 技术社区 › Agent_9191

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

dynamic-sql pivot sql-server

Agent_9191 · 技术社区 · 15 年前

我有三张桌子( 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,它将返回 NULL 对于为其他客户填充的任何首选项ID。如果运行查询时没有 PIVOT 表达式它返回按预期填充的所有首选项。只有当我透视数据时 无效的 爬进去。我希望我错过了一些简单的事情,但我没有看到错误。

1 回复 | 直到 15 年前

boydc7 15 年前

在CustomerID的1&2中,您看到Preference3默认值的唯一原因是不 TblCustomerPreference3的TblCustomerPreference记录,不是因为没有用于CustomerID=1/Preference3和CustomerID=2/Preference3组合的TblCustomerPreference记录。

在正确的联接条件中,仅在首选项值上指定仅在tblcustomerpreference和tblpreference之间联接-这将仅具体化来自具有不匹配记录任何 tblCustomerPreference中的customerID。如果您为该子句在customerid=@innerCustomerID上添加了一个附加的联接条件,那么现在您将执行您要执行的操作:即,给我所有首选项记录和任何匹配tblCustomerPreference for customerID=@innerCustomerID。

尝试一下,只需为customerID 1和preferrence3添加tblcustomerpreference记录,您就会注意到,对于customer2的preferrence3值,您不仅会看到空值,而且甚至不会再为customer 3得到结果。

看起来这正是您在WHERE子句中要做的,但是由于在查询处理期间在WHERE子句之前处理联接(遵循语句处理的正确逻辑顺序),所以您得到的中间结果集严格基于首选项组合,而不是客户和首选项组合。n.名词

所以,一些小的变化,你应该是好的。基本上,只需在右边的join子句中添加一个附加条件,指定一个特定的客户,即@innerrcustomed,然后删除整个where子句,就可以完成所有设置。请注意,这还将产生一个副作用,即实际返回传递给甚至不作为客户存在的任何@customerid的所有默认值-如果要将其更改为不为不存在的客户返回任何值,只需在查询之前添加一个检查,或包含一个where exists()筛选器:

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