SQL Server中如何从关联表动态获取对应SIZEPOS索引的SIZE列

嘿，我明白你现在的需求了——要根据主表的SIZEPOS字段，动态关联取SIZELIST表里对应的SIZE列，而不是硬编码死SIZE1对吧？在SQL Server里有两种靠谱的方法，我给你一步步讲清楚：

方法1：使用CASE表达式（最直接，适合固定列数） #

因为你的SIZE列是固定的SIZE1到SIZE25，这种情况下用CASE表达式是最简单的方案，不需要用到动态SQL，可读性和维护性都不错：

SELECT 
    sub.ITEID, 
    sub.SUBSTITUTECODE, 
    prod.MAINSZLID, 
    sub.SIZEPOS,
    -- 根据SIZEPOS动态匹配对应的SIZE列
    CASE sub.SIZEPOS
        WHEN 1 THEN siz.SIZE1
        WHEN 2 THEN siz.SIZE2
        WHEN 3 THEN siz.SIZE3
        WHEN 4 THEN siz.SIZE4
        WHEN 5 THEN siz.SIZE5
        WHEN 6 THEN siz.SIZE6
        WHEN 7 THEN siz.SIZE7
        WHEN 8 THEN siz.SIZE8
        WHEN 9 THEN siz.SIZE9
        WHEN 10 THEN siz.SIZE10
        WHEN 11 THEN siz.SIZE11
        WHEN 12 THEN siz.SIZE12
        WHEN 13 THEN siz.SIZE13
        WHEN 14 THEN siz.SIZE14
        WHEN 15 THEN siz.SIZE15
        WHEN 16 THEN siz.SIZE16
        WHEN 17 THEN siz.SIZE17
        WHEN 18 THEN siz.SIZE18
        WHEN 19 THEN siz.SIZE19
        WHEN 20 THEN siz.SIZE20
        WHEN 21 THEN siz.SIZE21
        WHEN 22 THEN siz.SIZE22
        WHEN 23 THEN siz.SIZE23
        WHEN 24 THEN siz.SIZE24
        WHEN 25 THEN siz.SIZE25
        ELSE NULL -- 处理SIZEPOS不在1-25范围内的异常情况
    END AS DynamicSize
FROM @SUBSTITUTE AS sub 
INNER JOIN @MATERIAL AS prod ON prod.ID = sub.ITEID 
INNER JOIN @SIZELIST AS siz ON siz.CODEID = prod.MAINSZLID;

这个方案的优点是直观，不需要额外的动态代码，而且执行计划稳定，适合列数固定的场景（你的情况刚好符合）。

方法2：动态SQL（适合列数可能扩展的场景） #

如果以后SIZE列的数量可能继续增加，不想每次手动修改CASE语句，那可以用动态SQL来自动生成匹配逻辑。不过要注意表变量在动态SQL的作用域中不可见，所以需要把示例中的表变量换成临时表（比如#SUBSTITUTE、#MATERIAL、#SIZELIST），或者在SQL Server 2016及以上版本中使用sp_executesql传递表参数。

下面是自动生成CASE逻辑的动态SQL示例：

DECLARE @SQL NVARCHAR(MAX)
DECLARE @CaseClause NVARCHAR(MAX)

-- 自动生成1到25的CASE分支
WITH NumberSequence AS (
    SELECT 1 AS Num
    UNION ALL
    SELECT Num + 1 FROM NumberSequence WHERE Num < 25
)
SELECT @CaseClause = STRING_AGG(
    CONCAT('WHEN ', Num, ' THEN siz.SIZE', Num),
    ' '
)
FROM NumberSequence;

-- 构建完整的查询语句
SET @SQL = CONCAT(N'
SELECT 
    sub.ITEID, 
    sub.SUBSTITUTECODE, 
    prod.MAINSZLID, 
    sub.SIZEPOS,
    CASE sub.SIZEPOS
        ', @CaseClause, '
        ELSE NULL
    END AS DynamicSize
FROM #SUBSTITUTE AS sub 
INNER JOIN #MATERIAL AS prod ON prod.ID = sub.ITEID 
INNER JOIN #SIZELIST AS siz ON siz.CODEID = prod.MAINSZLID;
')

-- 执行动态SQL
EXEC sp_executesql @SQL;

注意事项： #

1. 如果你的SQL Server版本低于2017，STRING_AGG函数不可用，需要改用FOR XML PATH来拼接字符串：

SELECT @CaseClause = STUFF((
    SELECT CONCAT(' WHEN ', Num, ' THEN siz.SIZE', Num)
    FROM NumberSequence
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

2. 动态SQL要注意SQL注入风险，这里我们用系统生成的数字序列，不存在注入风险，放心使用。

内容的提问来源于stack exchange，提问作者Faye D.