数据库中通用表函数递归调用的问题

select * from t_tree where node_name= N'辽宁省' --1

这句中要把父级全部列出，比如parent_id is null或parent_id=0这类的条件

function sumShuzi(&$tree, &$updateData = array()) {

  $sum = 0

  // foreach($tree as $key => $item) {  //这句话有毒

  foreach($tree as $key => &$item) {

    if(isset($item['children'])) {

      $oldPshuzi = $tree[$key]['Pshuzi']

      $tree[$key]['Pshuzi'] = sumShuzi($item['children'], $updateData)

      if($oldPshuzi != $tree[$key]['Pshuzi']) {

        $updateData[$item['id']] = array($tree[$key]['Pshuzi'], $tree[$key]['Pname_ch'])

      }

    }

    $sum += $tree[$key]['Pshuzi']

  }

  return $sum

}

$tree = json_decode('[{"id":"1","Pid":"0","Pname_ch":"\u6e20\u9053\u90e8","Pshuzi":"1638000","children":[{"id":"4","Pid":"1","Pname_ch":"\u9500\u552e\u4e8c\u90e8","Pshuzi":"895000","children":[{"id":"13","Pid":"4","Pname_ch":"\u5468\u7ecf\u7406","Pshuzi":"28","children":[{"id":"28","Pid":"13","Pname_ch":"\u6e56\u5357","Pshuzi":"158000"},{"id":"35","Pid":"13","Pname_ch":"\u65b0\u7586","Pshuzi":"19000"}]},{"id":"40","Pid":"4","Pname_ch":"\u9648\u7ecf\u7406","Pshuzi":"5000"}]}]}]', true)

//$tree是具有父子关系的数据树

sumShuzi($tree, $updateData)

foreach ($updateData as $id => $item) {

  $sql = "update your_table set Pshuzi={$item[0]} where id={$id}"

  mysqli_query($db, $sql)    //$db是你的数据库连接结果

}

数据库是什么数据库?

我先假设你是 Oracle 吧。

CREATE TABLE test_tree (

test_id INT NOT NULL,

pid INT,

test_val VARCHAR(10),

PRIMARY KEY (test_id)

)

INSERT INTO test_tree VALUES(1, NULL, '.NET')

INSERT INTO test_tree VALUES(2, 1, 'C#')

INSERT INTO test_tree VALUES(3, 1, 'J#')

INSERT INTO test_tree VALUES(4, 1, 'ASP.NET')

INSERT INTO test_tree VALUES(5, 1, 'VB.NET')

INSERT INTO test_tree VALUES(6, NULL, 'J2EE')

INSERT INTO test_tree VALUES(7, 6, 'EJB')

INSERT INTO test_tree VALUES(8, 6, 'Servlet')

INSERT INTO test_tree VALUES(9, 6, 'JSP')

INSERT INTO test_tree VALUES(10, NULL, 'Database')

INSERT INTO test_tree VALUES(11, 10,'DB2')

INSERT INTO test_tree VALUES(12, 10,'MySQL')

INSERT INTO test_tree VALUES(13, 10,'Oracle')

INSERT INTO test_tree VALUES(14, 10,'SQL Server')

INSERT INTO test_tree VALUES(15, 13,'PL/SQL')

INSERT INTO test_tree VALUES(16, 15,'Function')

INSERT INTO test_tree VALUES(17, 15,'Procedure')

INSERT INTO test_tree VALUES(18, 15,'Package')

INSERT INTO test_tree VALUES(19, 15,'Cursor')

INSERT INTO test_tree VALUES(20, 14,'T-SQL')

使用 START WITH CONNECT BY 语句实现树状查询

通过根节点 向下查询子节点

SELECT

LPAD(' ', 2*(LEVEL-1)) || test_val AS test_val

FROM

test_tree

START WITH

test_id IN (1, 6, 10)

CONNECT BY PRIOR test_id = pid

TEST_VAL

-----------------------------------------------------------

.NET

C#

J#

ASP.NET

VB.NET

J2EE

EJB

Servlet

JSP

Database

DB2

TEST_VAL

-----------------------------------------------------------

MySQL

Oracle

PL/SQL

Function

Procedure

Package

Cursor

SQL Server

T-SQL

20 rows selected.

---