有表
t1
字段有:id,name
1 a
2 b
t2
字段有:id,fid,name
1 1 aa
2 1 aaa
3 2 bb
4 2 bbb
问题是:
要求写一条sql语句能查询出形如树状结构记录;
如:结果:
a
--aa
--aaa
b
--bb
t1
字段有:id,name
1 a
2 b
t2
字段有:id,fid,name
1 1 aa
2 1 aaa
3 2 bb
4 2 bbb
问题是:
要求写一条sql语句能查询出形如树状结构记录;
如:结果:
a
--aa
--aaa
b
--bb
如:结果:
a
--aa
--aaa
b
--bb
--bbb
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
2 rows in set (0.00 sec)mysql> select * from t2;
+------+------+------+
| id | fid | name |
+------+------+------+
| 1 | 1 | aa |
| 2 | 1 | aaa |
| 3 | 2 | bb |
| 4 | 2 | bbb |
+------+------+------+
4 rows in set (0.00 sec)mysql> select name from
-> (
-> select id,id as fid,name,0 as sid
-> from t1
-> union all
-> select id,fid,concat('--',name),id as sid
-> from t2
-> ) t
-> order by fid,sid;
+-------+
| name |
+-------+
| a |
| --aa |
| --aaa |
| b |
| --bb |
| --bbb |
+-------+
6 rows in set (0.00 sec)mysql>
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
2 rows in set (0.00 sec)mysql> select * from t2;
+------+------+------+
| id | fid | name |
+------+------+------+
| 1 | 1 | aa |
| 2 | 1 | aaa |
| 3 | 2 | bb |
| 4 | 2 | bbb |
+------+------+------+
4 rows in set (0.02 sec)
select t1.name from t1 where t1.name='a' union select concat('--',t2.name) from t1,t2 where t1.id=t2.fid and t1.name='a' union select t1.name from t1 where t1.name='b' union select concat('--',t2.name) from t1,t2 where t1.id=t2.fid and t1.name='b' ;
.name) from t1,t2 where t1.id=t2.fid) t order by right(name,1),length(name);
+-------+
| name |
+-------+
| a |
| --aa |
| --aaa |
| b |
| --bb |
| --bbb |
+-------+
6 rows in set (0.00 sec)