sql數(shù)據(jù)庫存在父級、子級聯(lián)用的遞歸

    sql數(shù)據(jù)庫存在父級、子級聯(lián)用的遞歸，具體代碼如下：
    ----------------------------------------------------------------------------
    --sql 數(shù)據(jù)庫遞歸---在數(shù)據(jù)庫存在級聯(lián)用到，例如商品的父類和子類之間所屬關(guān)系
    ----------------------------------------------------------------------------
    WITH T AS ( SELECT
    * ,
    lv = 0 ,
    struct = CAST(id AS NVARCHAR) --struct 獲取的數(shù)據(jù)包含它本身和它的上級節(jié)點
    FROM
    tb_accounttype
    WHERE
    pid = 0
    UNION ALL
    SELECT
    a.* ,
    lv = ( b.lv + 1 ) ,
    struct = CAST(CAST(b.struct AS NVARCHAR) + ','
    + CAST(a.id AS NVARCHAR) AS NVARCHAR)
    FROM
    tb_accounttype a
    JOIN T b ON a.pid = b.AccountTypeNo
    )
    SELECT
    *
    FROM T
    -- 如果需要加入條件查詢例如查詢name等于你需要查詢的數(shù)據(jù)則加入一下代碼即可
    -- where T.name LIKE '%00%'
    OPTION
    ( MAXRECURSION 0 )