MySQL学习笔记-子查询和连接
mysql> UPDATE tdb_goods_types AS t1 INNER JOIN-> (SELECT p.type_id,p.type_name,count(s.type_name) AS child_count FROM tdb_goods_types AS p -> LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id -> GROUP BY p.type_name -> ORDER BY p.type_id)AS t2 -> ON t1.type_id = t2.type_id ->SET t1.child_count = t2.child_count;
mysql> SELECT * FROM tdb_goods_types;+---------+-----------------+-----------+-------------+| type_id | type_name | parent_id | child_count |+---------+-----------------+-----------+-------------+| 1 | 家用电器 | 0 | 2 || 2 | 电脑、办公 | 0 | 2 || 3 | 大家电 | 1 | 2 || 4 | 生活电器 | 1 | 2 || 5 | 平板电视 | 3 | 0 || 6 | 空调 | 3 | 0 || 7 | 电风扇 | 4 | 0 || 8 | 饮水机 | 4 | 0 || 9 | 电脑整机 | 2 | 3 || 10 | 电脑配件 | 2 | 2 || 11 | 笔记本 | 9 | 0 || 12 | 超级本 | 9 | 0 || 13 | 游戏本 | 9 | 0 || 14 | CPU | 10 | 0 || 15 | 主机 | 10 | 0 |+---------+-----------------+-----------+-------------+
页:
[1]