我有个问题:
表一:
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