原创

数据表content字段递归计算获取节点数据

SELECT
bb.name as '垂直组',
aa.id as '用例集ID',
aa.systems_and_services as '系统/业务',
aa.title as '用例集名称',
aa.review as '用例集状态',
aa.creator as '创建人',
aa.gmt_created as '创建时间',
aa.modifier as '更新人',
aa.gmt_modified as '更新时间',
aa.description as '业务价值'
FROM  test_case aa
left join case_organization bb on aa.product_line_id=bb.id
where aa.case_type=1 and aa.is_delete=0 and aa.case_content  like '%同上流程验证%'
order by aa.gmt_created desc

select distinct aa.creator FROM  test_case aa

select  * FROM  test_case aa where aa.creator='' or aa.creator is null

SELECT
cc.name as '垂直组',
aa.id as '用例集ID',
aa.systems_and_services as '系统/模块',
aa.title as '用例集名称',
bb.title as '测试任务名称',
bb.creator as '任务创建人',
bb.gmt_created as '创建时间',
bb.executors as '执行人',
bb.total_count as '任务用例数',
bb.pass_count as '已执行数',
bb.success_count as '成功数',
bb.fail_count as '缺陷数',
bb.expect_start_time as '测试开始时间', -- 这个不好弄
bb.expect_end_time as '测试结束时间', -- 这个不好弄
TIMESTAMPDIFF(DAY,bb.expect_start_time,bb.expect_end_time) as '测试周期',  -- 测试开始时间减去测试结束时间
bb.owner as '任务负责人'
FROM test_case aa
left join exec_record bb  on aa.id = bb.case_id
left join case_organization cc on aa.product_line_id=cc.id
where bb.is_delete=0  and aa.case_type = 1 order by bb.gmt_modified desc

-- 递归函数
WITH RECURSIVE tree_traversal AS (
    SELECT
        JSON_EXTRACT(case_content, '$.root') AS node,
                creator as 用例集创建人,
                title as 用例集名称,
        CAST('$.root' AS CHAR(10000)) AS path,
        0 AS depth
    FROM test_case
        -- where  title = '【测试】【PLM】版单&货品导出优化'
        where case_content is not null and case_type = 1 and is_delete = 0
        and case_content like '{%'  -- 过滤非法数据
        -- and title = '【测试】【PLM】版单&货品导出优化'
    UNION ALL
    SELECT
        JSON_EXTRACT(child, '$'),
                t.用例集创建人用例集创建人,
                t.用例集名称用例集名称,
        CONCAT(t.path, '.children[', idx, ']'),  -- 正确引用 idx
        t.depth + 1
    FROM tree_traversal t,
        JSON_TABLE(
            t.node,
            '$.children[*]' COLUMNS (
                child JSON PATH '$',
                idx FOR ORDINALITY  -- 列名定义修正
            )
        ) AS children
    WHERE JSON_LENGTH(t.node, '$.children') > 0
),

-- 详细数据
data_detail as (
SELECT
    -- JSON_UNQUOTE(JSON_EXTRACT(node, '$.data.id')) AS leaf_id,
        用例集名称,
    JSON_UNQUOTE(JSON_EXTRACT(node, '$.data.text')) AS 用例内容,

        FROM_UNIXTIME(JSON_EXTRACT(node, '$.data.created') / 1000) AS 创建时间,

    -- JSON_EXTRACT(node, '$.data.created') AS created,
        case
          when JSON_EXTRACT(node, '$.data.creator') is null or JSON_EXTRACT(node, '$.data.creator') = '' then 用例集创建人
          else regexp_replace(JSON_EXTRACT(node, '$.data.creator'), '"', '')
        end  AS 创建人
FROM tree_traversal
WHERE JSON_LENGTH(node, '$.children') = 0
)

select distinct 创建人 from data_detail  order by 创建时间 desc

正文到此结束

所属分类：测试技术

本文标签： Java Mysql sql
版权声明： 本站原创文章，于2025年05月08日由小蜜蜂社区发布，转载请注明出处

数据表content字段递归计算获取节点数据

热门推荐

相关文章

说给你听

本文目录

标签云

近期评论

网站信息