#
# Table structure for table 'ta'
#CREATE TABLE ta (
ID tinyint(3) unsigned NOT NULL auto_increment,
P_ID tinyint(3) unsigned default NULL,
NAME char(3) default NULL,
PRIMARY KEY (ID),
KEY P_ID (P_ID)
) TYPE=MyISAM;#
# Dumping data for table 'ta'
#INSERT INTO ta VALUES("1", NULL, "A");
INSERT INTO ta VALUES("2", NULL, "B");
INSERT INTO ta VALUES("3", NULL, "C");
INSERT INTO ta VALUES("4", NULL, "D");
INSERT INTO ta VALUES("5", NULL, "E");
INSERT INTO ta VALUES("6", NULL, "F");
INSERT INTO ta VALUES("7", "1", "AA");
INSERT INTO ta VALUES("8", "1", "AB");
INSERT INTO ta VALUES("9", "1", "AC");
INSERT INTO ta VALUES("10", "2", "BA");
INSERT INTO ta VALUES("11", "2", "BB");
INSERT INTO ta VALUES("12", "2", "BC");
mysql> SELECT * FROM `ta`;
+----+------+------+
| ID | P_ID | NAME |
+----+------+------+
| 1 | NULL | A |
| 2 | NULL | B |
| 3 | NULL | C |
| 4 | NULL | D |
| 5 | NULL | E |
| 6 | NULL | F |
| 7 | 1 | AA |
| 8 | 1 | AB |
| 9 | 1 | AC |
| 10 | 2 | BA |
| 11 | 2 | BB |
| 12 | 2 | BC |
+----+------+------+
12 rows in set (0.00 sec)mysql> SELECT * FROM ta A LEFT JOIN ta B ON A.ID = B.P_ID WHERE B.ID IS NOT NULL
;
+----+------+------+------+------+------+
| ID | P_ID | NAME | ID | P_ID | NAME |
+----+------+------+------+------+------+
| 1 | NULL | A | 7 | 1 | AA |
| 1 | NULL | A | 8 | 1 | AB |
| 1 | NULL | A | 9 | 1 | AC |
| 2 | NULL | B | 10 | 2 | BA |
| 2 | NULL | B | 11 | 2 | BB |
| 2 | NULL | B | 12 | 2 | BC |
+----+------+------+------+------+------+
6 rows in set (0.00 sec)mysql>
# Table structure for table 'ta'
#CREATE TABLE ta (
ID tinyint(3) unsigned NOT NULL auto_increment,
P_ID tinyint(3) unsigned default NULL,
NAME char(3) default NULL,
PRIMARY KEY (ID),
KEY P_ID (P_ID)
) TYPE=MyISAM;#
# Dumping data for table 'ta'
#INSERT INTO ta VALUES("1", NULL, "A");
INSERT INTO ta VALUES("2", NULL, "B");
INSERT INTO ta VALUES("3", NULL, "C");
INSERT INTO ta VALUES("4", NULL, "D");
INSERT INTO ta VALUES("5", NULL, "E");
INSERT INTO ta VALUES("6", NULL, "F");
INSERT INTO ta VALUES("7", "1", "AA");
INSERT INTO ta VALUES("8", "1", "AB");
INSERT INTO ta VALUES("9", "1", "AC");
INSERT INTO ta VALUES("10", "2", "BA");
INSERT INTO ta VALUES("11", "2", "BB");
INSERT INTO ta VALUES("12", "2", "BC");
mysql> SELECT * FROM `ta`;
+----+------+------+
| ID | P_ID | NAME |
+----+------+------+
| 1 | NULL | A |
| 2 | NULL | B |
| 3 | NULL | C |
| 4 | NULL | D |
| 5 | NULL | E |
| 6 | NULL | F |
| 7 | 1 | AA |
| 8 | 1 | AB |
| 9 | 1 | AC |
| 10 | 2 | BA |
| 11 | 2 | BB |
| 12 | 2 | BC |
+----+------+------+
12 rows in set (0.00 sec)mysql> SELECT * FROM ta A LEFT JOIN ta B ON A.ID = B.P_ID WHERE B.ID IS NOT NULL
;
+----+------+------+------+------+------+
| ID | P_ID | NAME | ID | P_ID | NAME |
+----+------+------+------+------+------+
| 1 | NULL | A | 7 | 1 | AA |
| 1 | NULL | A | 8 | 1 | AB |
| 1 | NULL | A | 9 | 1 | AC |
| 2 | NULL | B | 10 | 2 | BA |
| 2 | NULL | B | 11 | 2 | BB |
| 2 | NULL | B | 12 | 2 | BC |
+----+------+------+------+------+------+
6 rows in set (0.00 sec)mysql>
解决方案 »
- MySQL 忘记root密码怎么办(windows)
- mysql 存储过程能返回多个表吗?
- 如何下在MYSQL数据库,在哪下载。
- 再问,还没解决(同一字段插入到两个表,主要是还有别的数据分别插入!last_id()怎么用)
- 如何建立一个知识数据库,将储存最大化?!
- MYSQL无法使用远程连接
- 这样为什么不行.select * from table1 where table1.aa in (select aa from table2)
- 在Mysql中如何配置实现两台机器数据库《双向》同步!急!!!
- time类型的数据总是存储出错
- Mysql服务无缘无故被删除,求解决办法?
- 把一个字段加了索引以后,模糊查询(比如field like 'aaa%'),有的查询不出来
- 请问MySQL/Postgresql两者之间是怎么个分别的?是名字不同吗?
然后递归出来形成树!
的方法只能列出一级的 - -
而我的目录有N级.(也就是还有记录会隶属于AA,AB,AC...,下面还有记录)我的目录结构是这样+----+------+------+
| ID | P_ID | NAME |
+----+------+------+
| 1 | 0 | A |
| 2 | 0 | B |
| 3 | 1 | AA |
| 4 | 1 | AB |
| 5 | 2 | AC |
| 6 | 5 | BA |
| 7 | 5 | BB |
| 8 | 7 | BC |
| 9 | 8 | kkk |
| 10 | 9 | lll |
| 11 | 10 | mmm |
| 12 | 10 | nnn |
+----+------+------+简单是这样, P_ID=0即是根目录
现在随便给一个ID,比如 7 ,要求算出隶属于该ID以下的记录条数,(全部.有关系的都计算,不仅仅只是计算下面一级的..)
select * from table where p_id = xx 不就可以了?何必这么辛苦咧.要 where p_id=xx 然后 列出的记录下的记录也计算在内的..
帮我解决一下问题
我天天给你们烧高香....拜托了 :~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<
那就用几个 LEFT JOIN 吧:)