Добрый день. На своём примере на всякий случай продемонстрирую, чтоб в полях не путаться.
CREATE TABLE `t4061` (`category_id` int(11) not null default '0',`name` varchar(255) DEFAULT NULL,parent_id int default NULL) ENGINE=MyISAM;
insert into `t4061` values
(1,'Category 1',NULL)
,(2,'Category 2',NULL)
,(3,'Category 3',NULL)
,(4,'Category 4 (parent - Category 1)',1)
,(5,'Category 5 (parent - Category 2)',2)
,(6,'Category 6 (parent - Category 2)',2)
,(7,'Category 7 (parent - Category 3)',3)
,(8,'Category 8 (parent - Category 3)',3)
,(9,'Category 9 (parent - Category 3)',3)
,(10,'Category 10 (parent - Category 3)',3);
mysql> select * from `t4061`;
+-------------+-----------------------------------+-----------+
| category_id | name | parent_id |
+-------------+-----------------------------------+-----------+
| 1 | Category 1 | NULL |
| 2 | Category 2 | NULL |
| 3 | Category 3 | NULL |
| 4 | Category 4 (parent - Category 1) | 1 |
| 5 | Category 5 (parent - Category 2) | 2 |
| 6 | Category 6 (parent - Category 2) | 2 |
| 7 | Category 7 (parent - Category 3) | 3 |
| 8 | Category 8 (parent - Category 3) | 3 |
| 9 | Category 9 (parent - Category 3) | 3 |
| 10 | Category 10 (parent - Category 3) | 3 |
+-------------+-----------------------------------+-----------+
10 rows in set (0.00 sec)
Количество подкатегорий - select count(category_id) from `t4061` where parent_id=НУЖНЫЙ_ID;
Количество корневых категорий - select count(category_id) from `t4061` where isnull(parent_id);
mysql> select count(*) from `t4061` where isnull(parent_id);
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
mysql> select count(category_id) from `t4061` where parent_id=1;
+--------------------+
| count(category_id) |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)
mysql> select count(category_id) from `t4061` where parent_id=2;
+--------------------+
| count(category_id) |
+--------------------+
| 2 |
+--------------------+
1 row in set (0.00 sec)
mysql> select count(category_id) from `t4061` where parent_id=3;
+--------------------+
| count(category_id) |
+--------------------+
| 4 |
+--------------------+
1 row in set (0.00 sec)
Сами подкатегории:[syntax=mysql]select * from `t4061` where parent_id=НУЖНЫЙ_ID;[/mysql]
Корневые категории:[syntax=mysql]select * from `t4061` where isnull(parent_id)[/mysql]
mysql> select * from `t4061` where isnull(parent_id);
+-------------+------------+-----------+
| category_id | name | parent_id |
+-------------+------------+-----------+
| 1 | Category 1 | NULL |
| 2 | Category 2 | NULL |
| 3 | Category 3 | NULL |
+-------------+------------+-----------+
3 rows in set (0.15 sec)
mysql> select * from `t4061` where parent_id=1;
+-------------+----------------------------------+-----------+
| category_id | name | parent_id |
+-------------+----------------------------------+-----------+
| 4 | Category 4 (parent - Category 1) | 1 |
+-------------+----------------------------------+-----------+
1 row in set (0.00 sec)
mysql> select * from `t4061` where parent_id=2;
+-------------+----------------------------------+-----------+
| category_id | name | parent_id |
+-------------+----------------------------------+-----------+
| 5 | Category 5 (parent - Category 2) | 2 |
| 6 | Category 6 (parent - Category 2) | 2 |
+-------------+----------------------------------+-----------+
2 rows in set (0.00 sec)
mysql> select * from `t4061` where parent_id=3;
+-------------+-----------------------------------+-----------+
| category_id | name | parent_id |
+-------------+-----------------------------------+-----------+
| 7 | Category 7 (parent - Category 3) | 3 |
| 8 | Category 8 (parent - Category 3) | 3 |
| 9 | Category 9 (parent - Category 3) | 3 |
| 10 | Category 10 (parent - Category 3) | 3 |
+-------------+-----------------------------------+-----------+
4 rows in set (0.00 sec)
Отредактированно deadka (04.04.2011 17:04:32)
