我有个问题:
表一:

SELECT OrgNode as 'hierarchyid'
, OrgLevel as "non-type '
, UniCODOBJ as 'uniqueidentifier'
, BitREQUIRED as "bit"
, StrField as 'nvarchar (50)'
, StrTITLE_FIELD as 'nvarchar (50)'
, IntCODE_LENGTH as 'int'
OF BOX_HIERARCHY


我有查询:

DECLARE @CurrentNode hierarchyid
SELECT @ CurrentNode = OrgNode
OF BOX_HIERARCHY
where uniCODOBJ = 'CBC33732-2E4C-4103-887A-B968E53C0E05';

SELECT OrgNode, OrgNode.ToString ()
, OrgLevel
, uniCODOBJ
, bitREQUIRED
, strField
, strTITLE_FIELD
, intCODE_LENGTH
of BOX_HIERARCHY
where OrgNode.IsDescendantOf (@CurrentNode) = 1


但是,此查询返回:

OrgNode OrgNode.string OrgLevel BitRequired
0x78 / 3/1 1
0x7AC0 / 3/1/2 1
0x7AD6 / 3/1/1/3 0
0x7AD6B0 / 3/1/1/1/4 0
0x7AD6B580 / 3/1/1/1/1/5 1
0x7AD6B5AC / 3/1/1/1/1/1/6 1


因为OrgNode-> 0x78具有uniCODOBJ:CBC33732-2E4C-4103-887A-B968E53C0E05

我的问题在哪里?
直子是唯一必要的:

与OrgNode-> 0x78只有1个直接子代

OrgNode OrgNode.string OrgLevel位必需

0x78 / 3/1 1
0x7AC0 / 3/1/2 1


使用OrgNode-> 0x7AC0,带有3个可能的直接子级
OrgNode OrgNode.string OrgLevel位必需

0x7AC0 / 3/1/2 1
0x7AD6 / 3/1/1/3 0
0x7AD6B0 / 3/1/1/1/4 0
0x7AD6B580 / 3/1/1/1/1/5 1


我很想获得查询,以获取所有可能的直接子查询
但该查询返回了我所有亲戚孩子
如果我必须经历0x7AD6B5AC情况下的3个级别,处于6级,而3个级别至少为0x78,并进行0x78的协商,它将返回我所有的内容,我不在乎。

最佳答案

自己发布解决方案:

ALTER PROCEDURE [dbo].[GET_CHILD_REPOS] @id uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @hi hierarchyid;
    SELECT @hi = OrgNode FROM BOX_HIERARCHY
    WHERE uniCODOBJ = @id

    SELECT h.OrgNode.ToString() AS OrgNode, h.OrgLevel, h.uniCODOBJ, h.bitREQUIRED,
    strFIELD, t.strARCTAB, t.strNOMOBJ
    FROM BOX_HIERARCHY h
    INNER JOIN tabTABLAS t
    ON h.uniCODOBJ = t.uniCODPAD
    WHERE OrgNode.IsDescendantOf(@hi) = 1
    AND OrgNode <> @hi
    AND OrgLevel <=
    (
        SELECT MIN(OrgLevel)
        FROM
        (
            SELECT h.OrgNode.ToString() AS OrgNode, h.OrgLevel, h.uniCODOBJ, h.bitREQUIRED,
            strFIELD, t.strARCTAB, t.strNOMOBJ
            FROM BOX_HIERARCHY h
            INNER JOIN tabTABLAS t
            ON h.uniCODOBJ = t.uniCODPAD
            WHERE OrgNode.IsDescendantOf(@hi) = 1
            AND OrgNode <> @hi
        ) a
        WHERE bitREQUIRED = 1
    )
    ORDER BY OrgNode

END

09-06 12:02