如何用T-SQL从主表及两个子表生成指定格式JSON字符串？

可以通过关联主表与子表，结合FOR JSON PATH语法构建嵌套JSON结构，具体代码如下：

SELECT
    s.Student,
    s.Rollno AS RollNo,
    -- 生成Terminal-1的成绩数组
    (
        SELECT t1.Subject1, t1.Subject2, t1.Subject3
        FROM [Terminal-1] t1
        WHERE t1.Rollno = s.Rollno
        FOR JSON PATH
    ) AS [Terminal-1],
    -- 生成Terminal-2的成绩数组
    (
        SELECT t2.Subject1, t2.Subject2, t2.Subject3
        FROM [Terminal-2] t2
        WHERE t2.Rollno = s.Rollno
        FOR JSON PATH
    ) AS [Terminal-2]
FROM Student s
FOR JSON PATH, ROOT('');

代码说明 #

• 主查询从Student表获取学生姓名和学号，通过AS RollNo指定JSON中的键名，与需求格式匹配
• 两个子查询分别关联对应的终端成绩表，筛选出当前学生的成绩记录，FOR JSON PATH会自动将查询结果转换为JSON数组
• 主查询末尾的ROOT('')用于移除默认生成的根节点，最终输出符合要求的数组格式JSON

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