我的表(dbTreeNode)的字段如下: tn_id, tn_name, tn_lft, tn_rgt, tn_lvl其中tn_id, tn_name, tn_lft, tn_rgt这四个字段是有值的,而tn_lvl是要更新的字段,例如:tn_id tn_name tn_lft tn_rgt tn_lvl
1 网站首页 1 6
2 产品栏目 2 5
3 智能手机 3 4 树的结构如下:
网站首页
+-产品栏目
+-智能手机现在要根据以下这条SQL语句来更新tn_lvl(树的结点的层数)这个字段:
SELECT COUNT(a2.tn_lft) AS lvl, a1.tn_id
FROM dbTreeNode a1, dbTreeNode a2
WHERE a1.tn_lft BETWEEN a2.tn_lft AND a2.tn_rgt
GROUP BY a1.tn_id, a1.tn_lft
ORDER BY a1.tn_lft执行此SQL语句后的结果如下:
lvl tn_id
1 1
2 2
3 3现在我想要将上面的lvl列的结果,通过一条UPDATE语句直接更新到dbTreeNode的tn_lvl字段中,但是怎么测试都不成功!!!UPDATE dbTreeNode SET tn_lvl = t1.lvl
FROM (
SELECT COUNT(a2.tn_lft) AS lvl, a1.tn_id
FROM dbTreeNode a1, dbTreeNode a2
WHERE a1.tn_lft BETWEEN a2.tn_lft AND a2.tn_rgt
GROUP BY a1.tn_id, a1.tn_lft
ORDER BY a1.tn_lft
) AS t1 , dbTreeNode AS t2
WHERE t1.tn_id=t2.tn_id
1 网站首页 1 6
2 产品栏目 2 5
3 智能手机 3 4 树的结构如下:
网站首页
+-产品栏目
+-智能手机现在要根据以下这条SQL语句来更新tn_lvl(树的结点的层数)这个字段:
SELECT COUNT(a2.tn_lft) AS lvl, a1.tn_id
FROM dbTreeNode a1, dbTreeNode a2
WHERE a1.tn_lft BETWEEN a2.tn_lft AND a2.tn_rgt
GROUP BY a1.tn_id, a1.tn_lft
ORDER BY a1.tn_lft执行此SQL语句后的结果如下:
lvl tn_id
1 1
2 2
3 3现在我想要将上面的lvl列的结果,通过一条UPDATE语句直接更新到dbTreeNode的tn_lvl字段中,但是怎么测试都不成功!!!UPDATE dbTreeNode SET tn_lvl = t1.lvl
FROM (
SELECT COUNT(a2.tn_lft) AS lvl, a1.tn_id
FROM dbTreeNode a1, dbTreeNode a2
WHERE a1.tn_lft BETWEEN a2.tn_lft AND a2.tn_rgt
GROUP BY a1.tn_id, a1.tn_lft
ORDER BY a1.tn_lft
) AS t1 , dbTreeNode AS t2
WHERE t1.tn_id=t2.tn_id
UPDATE A
SET tn_lvl = B.lvl
FROM dbTreeNode A
inner join
(
SELECT COUNT(a2.tn_lft) AS lvl, a1.tn_id
FROM dbTreeNode a1, dbTreeNode a2
WHERE a1.tn_lft BETWEEN a2.tn_lft AND a2.tn_rgt
GROUP BY a1.tn_id, a1.tn_lft
) B
ON A.tn_id=B.tn_id
FROM (
SELECT COUNT(a2.tn_lft) AS lvl, a1.tn_id
FROM dbTreeNode a1, dbTreeNode a2
WHERE a1.tn_lft BETWEEN a2.tn_lft AND a2.tn_rgt
GROUP BY a1.tn_id, a1.tn_lft
) AS t1 , dbTreeNode AS t2
WHERE t1.tn_id=t2.tn_id