如何在SQL中基于tbl1的顺序重新排列tbl2的JSON数组？

你的需求是将@tbl2按照@tbl1的顺序重新排列：先保留@tbl1中元素的顺序，再追加@tbl2中不在@tbl1里的元素（保持它们在@tbl2中的原有顺序）。

你当前的代码仅通过JOIN取出了两个数组的交集（A、B、C），完全丢失了@tbl2里的X和D，这就是没达到预期的核心原因。

下面是修正后的代码，可实现你要的效果：

DECLARE @tbl1 VARCHAR(MAX) = '["A","B","C"]';
DECLARE @tbl2 VARCHAR(MAX) = '["X","A","D","C","B"]';

-- 解析@tbl1，记录每个值对应的原数组顺序索引
WITH Tbl1Ordered AS (
    SELECT 
        Value,
        CAST([key] AS INT) AS SortOrder
    FROM OPENJSON(@tbl1)
),
-- 解析@tbl2，记录每个值及它在原数组的索引
Tbl2All AS (
    SELECT 
        Value,
        CAST([key] AS INT) OriginalIndex
    FROM OPENJSON(@tbl2)
)
-- 生成排序后的结果并转为JSON数组
SELECT Value
FROM Tbl2All
LEFT JOIN Tbl1Ordered ON Tbl2All.Value = Tbl1Ordered.Value
-- 排序规则：@tbl1中的元素按其原有顺序排，不在@tbl1的元素按@tbl2原顺序追加在后
ORDER BY 
    CASE WHEN Tbl1Ordered.SortOrder IS NOT NULL THEN Tbl1Ordered.SortOrder ELSE 9999 END,
    OriginalIndex
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

代码说明： #

1. Tbl1Ordered：将@tbl1解析为表结构，存储每个元素的值和它在原数组中的索引（作为排序依据）。
2. Tbl2All：将@tbl2解析为表结构，存储每个元素的值和它在原数组中的索引（用于保留非交集元素的原有顺序）。
3. 排序逻辑：
   • 属于@tbl1的元素，用@tbl1的索引排序，保证和@tbl1顺序一致；
   • 不属于@tbl1的元素，用一个足够大的固定值（如9999）确保排在后面，再按它们在@tbl2中的原索引排序，维持原有顺序。
4. JSON输出设置：WITHOUT_ARRAY_WRAPPER用于去除默认的外层对象包裹，直接生成纯JSON数组。

执行后输出结果与你的期望一致：

["A","B","C","X","D"]

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