如何在SQL Server中不使用游标生成指定层级JSON数据？

完全理解你的困扰，其实不用游标也能轻松搞定这个需求！SQL Server自带的JSON函数就能帮你直接生成这种嵌套结构的JSON数据，比游标高效多了。

假设你的数据表名为Reports，包含字段id、userid、reportYear、reportMonth（对应你提供的数据快照字段），针对userid = 1的情况，你可以用下面的SQL语句直接生成目标格式：

SELECT
    reportYear AS 'name',
    -1 AS 'id',
    (
        -- 子查询生成每个年份对应的月度子节点数组
        SELECT
            CONCAT(FORMAT(reportMonth, '00'), '-', reportYear) AS 'name',
            id AS 'id'
        FROM Reports
        WHERE userid = 1 
          AND reportYear = outer_query.reportYear
        FOR JSON PATH
    ) AS 'children'
FROM (
    -- 先取出userid=1的所有唯一年份
    SELECT DISTINCT reportYear
    FROM Reports
    WHERE userid = 1
) AS outer_query
FOR JSON PATH;

代码细节说明： #

• 外层查询通过DISTINCT获取userid=1的所有不重复年份，作为父节点的name，并固定id为-1
• 子查询针对每个年份做以下处理：
  • 月份格式化：用FORMAT(reportMonth, '00')把月份转换为两位数字（SQL Server 2016及以上版本支持）；如果是更早版本，可替换为RIGHT('00' + CAST(reportMonth AS VARCHAR(2)), 2)
  • 拼接成mm-yyyy格式的name，同时取出对应记录的id
  • 用FOR JSON PATH把这些记录转换为JSON数组，作为父节点的children字段
• 最后外层的FOR JSON PATH会把所有父节点组合成一个完整的JSON数组，正好匹配你需要的格式

执行这段SQL后，得到的结果就是可以直接赋值给var data的JSON结构，和你给出的示例完全一致。

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