移除可选模块代码后SQL返回结果增多的问题排查

嘿，我来帮你梳理下这个存储过程的问题——带可选模块逻辑时只返回19条结果，注释掉后能返回31条，而且模块授权状态的判断也不符合预期，对吧？咱们一步步拆解问题，找到解决办法。

核心问题分析 #

从你的描述和代码片段来看，问题大概率出在数据连接方式或XML授权模块的解析逻辑上：

• 注释掉可选模块代码后返回31条，说明这31条是你的基础数据集；
• 加入可选模块逻辑后只剩19条，说明你的连接方式（比如用了INNER JOIN）过滤掉了不匹配的行，而不是为每条基础数据标记授权状态；
• 另外，你没写完的@LicensedModules插入逻辑（从XML提取授权模块）可能存在解析错误，导致部分授权模块没被正确识别。

关键修正步骤 #

1. 先确保XML授权模块解析正确 #

首先要把XML里的授权模块ID正确提取到@LicensedModules中，这是判断授权状态的基础。假设你的XML结构类似这样（根据实际结构调整）：

<Licence>
  <Modules>
    <Module>1</Module>
    <Module>2</Module>
    <Module>6</Module>
  </Modules>
</Licence>

那提取代码应该是：

-- 从XML提取已授权的模块ID
INSERT INTO @LicensedModules (moduleid)
SELECT x.value('.', 'INT') -- 如果是属性值，比如<Module ID="1"/>，就改成x.value('@ID', 'INT')
FROM @xml.nodes('/Licence/Modules/Module') AS T(x)

验证：可以单独执行SELECT * FROM @LicensedModules，看看提取的模块ID是否和XML里的一致。

2. 选择正确的连接方式保留基础数据 #

如果你的需求是保留所有31条基础数据，同时为每条数据标记每个可选模块的授权状态，不能用INNER JOIN，要用CROSS JOIN（生成基础数据和可选模块的笛卡尔积）+LEFT JOIN（关联授权模块），再通过条件聚合把每个模块的状态转成列。

完整示例代码如下：

-- 1. 获取许可证XML
DECLARE @xml XML
SELECT TOP 1 @xml = CAST(LicenceKey AS xml) FROM Organisation

-- 2. 存储已授权的模块ID
DECLARE @LicensedModules TABLE (moduleid INT)
INSERT INTO @LicensedModules (moduleid)
SELECT x.value('.', 'INT') -- 替换成你的XML实际解析逻辑
FROM @xml.nodes('/Licence/Modules/Module') AS T(x)

-- 3. 定义可选模块列表
DECLARE @optionalmodules TABLE (moduleid INT, description VARCHAR(100))
INSERT INTO @optionalmodules (moduleid, description) VALUES 
(1,'R9'), (2,'S8'), (6,'S7'), (8,'A6'), 
(9,'C5'), (10,'S4'), (11,'A2'), (12,'P4'), (13,'PSL')

-- 4. 假设你的基础数据来自某个表（比如YourBaseTable），先获取31条基础数据
DECLARE @BaseData TABLE (ID INT, [OtherColumns] VARCHAR(50)) -- 替换成你的实际表结构
INSERT INTO @BaseData SELECT ID, [OtherColumns] FROM YourBaseTable

-- 5. 生成带授权状态的结果（保留所有31条基础数据）
SELECT
    bd.ID,
    bd.[OtherColumns],
    -- 每个模块单独显示授权状态（1=已授权，0=未授权）
    MAX(CASE WHEN om.moduleid = 1 THEN CASE WHEN lm.moduleid IS NOT NULL THEN 1 ELSE 0 END END) AS R9_Licensed,
    MAX(CASE WHEN om.moduleid = 2 THEN CASE WHEN lm.moduleid IS NOT NULL THEN 1 ELSE 0 END END) AS S8_Licensed,
    MAX(CASE WHEN om.moduleid = 6 THEN CASE WHEN lm.moduleid IS NOT NULL THEN 1 ELSE 0 END END) AS S7_Licensed,
    MAX(CASE WHEN om.moduleid = 8 THEN CASE WHEN lm.moduleid IS NOT NULL THEN 1 ELSE 0 END END) AS A6_Licensed,
    MAX(CASE WHEN om.moduleid = 9 THEN CASE WHEN lm.moduleid IS NOT NULL THEN 1 ELSE 0 END END) AS C5_Licensed,
    MAX(CASE WHEN om.moduleid = 10 THEN CASE WHEN lm.moduleid IS NOT NULL THEN 1 ELSE 0 END END) AS S4_Licensed,
    MAX(CASE WHEN om.moduleid = 11 THEN CASE WHEN lm.moduleid IS NOT NULL THEN 1 ELSE 0 END END) AS A2_Licensed,
    MAX(CASE WHEN om.moduleid = 12 THEN CASE WHEN lm.moduleid IS NOT NULL THEN 1 ELSE 0 END END) AS P4_Licensed,
    MAX(CASE WHEN om.moduleid = 13 THEN CASE WHEN lm.moduleid IS NOT NULL THEN 1 ELSE 0 END END) AS PSL_Licensed
FROM @BaseData bd
CROSS JOIN @optionalmodules om -- 让每条基础数据对应所有可选模块
LEFT JOIN @LicensedModules lm ON om.moduleid = lm.moduleid
GROUP BY bd.ID, bd.[OtherColumns]

如果你的需求是只返回所有可选模块的授权状态（共9条记录），那简化成左连接查询即可：

SELECT
    om.moduleid,
    om.description,
    CASE WHEN lm.moduleid IS NOT NULL THEN 1 ELSE 0 END AS IsLicensed
FROM @optionalmodules om
LEFT JOIN @LicensedModules lm ON om.moduleid = lm.moduleid

关键注意事项 #

• XML解析必须匹配实际结构：如果你的XML节点名、属性名和示例不一样，一定要调整nodes()和value()里的XPath路径，否则会提取不到数据；
• 避免内连接过滤数据：INNER JOIN会只保留两边都匹配的行，这就是你结果从31条变成19条的原因，要用LEFT JOIN或CROSS JOIN来保留所有需要的行；
• 分步验证：先单独验证@LicensedModules的数据，再验证连接后的中间结果，逐步排查问题。

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