现在有表sys_module,其中表结构为
moduleid 节点的ID modulename 节点名 parentid 父节点ID leaf 是否是叶子节点 level 该节点位于第几层 orderid 该节点为父节点下的第几个子节点现有数据如下:
moduleid modulename parentid leaf level orderid
1 菜单1 0 1 2 1
2 菜单2 0 1 2 2
11 菜单11 0 1 2 11
12 菜单12 1 0 3 1
13 菜单13 1 1 3 1
14 菜单14 11 1 3 1
215 菜单215 13 1 4 1
216 菜单216 14 0 4 1
217 菜单217 215 0 5 1我要写sql 将表的数据查出来 并且按照 level层数为2的数据分组
返回的结果为 sum为菜单下所有子节点的总和,如果level不为2,那么sum为空或者为0,并且根据parentid和orderid做排序
sum moduleid modulename parentid leaf level orderid
5 1 菜单1 0 1 2 1
12 菜单12 1 0 3 1
13 菜单13 1 1 3 2
215 菜单215 13 1 4 1
217 菜单217 215 0 5 1
1 2 菜单2 0 1 2 2
3 11 菜单11 0 1 2 11sql该如何写 90分 坐等高手 急 谢谢
moduleid 节点的ID modulename 节点名 parentid 父节点ID leaf 是否是叶子节点 level 该节点位于第几层 orderid 该节点为父节点下的第几个子节点现有数据如下:
moduleid modulename parentid leaf level orderid
1 菜单1 0 1 2 1
2 菜单2 0 1 2 2
11 菜单11 0 1 2 11
12 菜单12 1 0 3 1
13 菜单13 1 1 3 1
14 菜单14 11 1 3 1
215 菜单215 13 1 4 1
216 菜单216 14 0 4 1
217 菜单217 215 0 5 1我要写sql 将表的数据查出来 并且按照 level层数为2的数据分组
返回的结果为 sum为菜单下所有子节点的总和,如果level不为2,那么sum为空或者为0,并且根据parentid和orderid做排序
sum moduleid modulename parentid leaf level orderid
5 1 菜单1 0 1 2 1
12 菜单12 1 0 3 1
13 菜单13 1 1 3 2
215 菜单215 13 1 4 1
217 菜单217 215 0 5 1
1 2 菜单2 0 1 2 2
3 11 菜单11 0 1 2 11sql该如何写 90分 坐等高手 急 谢谢
moduleid, modulename, parentid, leaf, "level", orderid from
(select sum(1) over(partition by rootmoduleid) sum, t1.* from
(select t.*, connect_by_root moduleid rootmoduleid from t
start with "level"=2 connect by prior moduleid=parentid) t1
order by rootmoduleid, parentid, orderid);
WITH t AS
(SELECT 1 AS mId,
'菜单1' AS mName,
0 AS pid,
1 AS leaf,
2 AS lvl,
1 AS orderid
FROM dual
UNION ALL
SELECT 2, '菜单2', 0, 1, 2, 2 FROM dual
UNION ALL
SELECT 11, '菜单11', 0, 1, 2, 11 FROM dual
UNION ALL
SELECT 12, '菜单12', 1, 0, 3, 1 FROM dual
UNION ALL
SELECT 13, '菜单13', 1,1, 3, 1 FROM dual
UNION ALL
SELECT 14, '菜单14', 11, 1, 3, 1 FROM dual
UNION ALL
SELECT 215, '菜单215', 13, 1, 4, 1 FROM dual
UNION ALL
SELECT 216, '菜单216', 14, 0, 4, 1 FROM dual
UNION ALL
SELECT 217, '菜单217', 215, 0, 5, 1 FROM dual
)
SELECT mid,
mname,
pid,
leaf,
lvl,
orderid,
DECODE(lvl,2,aa,0) AS "SUM"
FROM
(SELECT mid,
mname,
pid,
leaf,
lvl,
orderid,
(SELECT COUNT(1)
FROM t t1
CONNECT BY prior t1.mid=t1.pid
START WITH t1.mid = a.mid
) AS aa
FROM
( SELECT * FROM t CONNECT BY prior mid = pid START WITH pid = 0
) a
)
MID MNAME PID LEAF LVL ORDERID SUM
---------------------- --------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
1 菜单1 0 1 2 1 5
12 菜单12 1 0 3 1 0
13 菜单13 1 1 3 1 0
215 菜单215 13 1 4 1 0
217 菜单217 215 0 5 1 0
2 菜单2 0 1 2 2 1
11 菜单11 0 1 2 11 3
14 菜单14 11 1 3 1 0
216 菜单216 14 0 4 1 0 选定了 9 行
楼上的 请问有没有简洁点的 如果我表里有100条数据或者100条以上的数据 那sql岂不是很长
搞错了,把t换成你的sys_moduleselect decode(moduleid, rootmoduleid, sum) sum,
moduleid, modulename, parentid, leaf, "level", orderid from
(select sum(1) over(partition by rootmoduleid) sum, t1.* from
(select sys_module.*, connect_by_root moduleid rootmoduleid from sys_module
start with "level"=2 connect by prior moduleid=parentid) t1
order by rootmoduleid, parentid, orderid);
学习了,还没用过这个函数。CONNECT_BY_ROOT 操作的功能就是获取根节点记录的字段信息。这个功能在 9i当中可以利用 SYS_CONNECT_BY_PATH来实现。