OutSystems Advanced SQL PIVOT查询筛选多激活选项设备求助

问题原因 #

你之前在内层查询加过滤条件会直接过滤掉未命中指定选项的行，导致PIVOT转换后只能保留过滤后的选项列，丢失了设备的其他选项状态。

解决方案 #

方案1：在PIVOT后的外层查询加过滤条件（最简便，适配现有代码） #

直接在外层查询增加WHERE子句，对转成列的指定选项做=1的判断即可，既保留全量选项的列数据，又能筛选出符合要求的设备，修改后代码示例如下：

SELECT MachineID, MachineSN ,@StringIn
FROM (    
    SELECT {Machine}.[Id] as MachineID,
            {Options}.[Name] as OptionName,
            {MachineOption}.[OptionActive] as IsActive,
            {Machine}.[SNumber] as MachineSN
    FROM {MachineOption}
    INNER JOIN {Options} ON {Options}.[Id] = {MachineOption}.[OptionId]
    INNER JOIN {Machine} ON {Machine}.[Id] = {MachineOption}.[MachineId]     
    GROUP BY {Options}.[Name], {MachineOption}.[OptionActive], {Machine}.[SNumber], {Machine}.[Id]
) R
Pivot (max(IsActive) for OptionName in (@StringIn)) as PivotedResult
-- 此处新增筛选条件，示例为筛选同时激活X、Y两个选项的设备
WHERE X = 1 AND Y = 1
Order By MachineID

如果你的@StringIn是动态拼接的列名，WHERE条件也可以同步动态拼接，直接生成指定选项1=1 AND 指定选项2=1 ...的格式追加即可。

方案2：预筛选符合条件的设备ID再关联（大数据量下性能更优） #

如果设备数据量较大，可以先单独查询出同时激活指定选项的设备ID集合，再关联回原表取全量选项数据，避免全量PIVOT后再过滤的性能损耗：

-- 预筛选符合多选项同时激活要求的设备ID
WITH TargetMachines AS (
    SELECT MachineId
    FROM {MachineOption}
    INNER JOIN {Options} ON {Options}.[Id] = {MachineOption}.[OptionId]
    WHERE {Options}.[Name] IN ('X','Y') -- 替换为你要筛选的指定选项列表
        AND {MachineOption}.[OptionActive] = 1
    GROUP BY MachineId
    -- 计数等于指定选项的总个数，确保所有指定选项都处于激活状态
    HAVING COUNT(DISTINCT {Options}.[Name]) = 2
)
-- 仅对符合条件的设备做PIVOT，保留全量选项状态
SELECT MachineID, MachineSN ,@StringIn
FROM (    
    SELECT m.[Id] as MachineID,
            o.[Name] as OptionName,
            mo.[OptionActive] as IsActive,
            m.[SNumber] as MachineSN
    FROM {MachineOption} mo
    INNER JOIN {Options} o ON o.[Id] = mo.[OptionId]
    INNER JOIN {Machine} m ON m.[Id] = mo.[MachineId]
    INNER JOIN TargetMachines tm ON tm.MachineId = m.[Id]
    GROUP BY o.[Name], mo.[OptionActive], m.[SNumber], m.[Id]
) R
Pivot (max(IsActive) for OptionName in (@StringIn)) as PivotedResult
Order By MachineID

内容的提问来源于stack exchange，提问作者Tiago