求写一个sql语句

[lfkcn]

有这样一张表folder：三个字段id,name,pid
现在需要这样的数据：
id，name,pname

其中pname是根据pid在folder这张表中查询到的，如果pid=0，那就显示空

比如数据如下：
id name pid
1 aa 0
2 bb 1
3 cc 2
4 dd 0

得到的数据应该是
id name pname
1 aa
2 bb aa
3 cc bb
4 dd

这样能直接用一个sql写出来吗？

[zuoxingyu]

select A.id,A.name,if(B.Name<=>null,'',B.Name) from TTT A left join TTT B on A.Pid=B.ID

[阿_布]

mysql> select * from folder;
+------+------+------+
| id   | name | pid  |
+------+------+------+
|    1 | aa   |    0 |
|    2 | bb   |    1 |
|    3 | cc   |    2 |
|    4 | dd   |    0 |
+------+------+------+
4 rows in set (0.02 sec)

mysql> select a.id,a.name,b.name
    -> from folder a left join folder b
    -> on a.pid=b.id;
+------+------+------+
| id   | name | name |
+------+------+------+
|    1 | aa   | NULL |
|    2 | bb   | aa   |
|    3 | cc   | bb   |
|    4 | dd   | NULL |
+------+------+------+
4 rows in set (0.02 sec)

[zuoxingyu]

mysql> select A.id,A.name,if(B.Name<=>null,'',B.Name) as Pname from TTT A left j
oin TTT B on A.Pid=B.ID;
+------+------+-------+
| id   | name | Pname |
+------+------+-------+
|    1 | aa   |       |
|    2 | bb   | aa    |
|    3 | cc   | bb    |
|    4 | dd   |       |
+------+------+-------+
4 rows in set (0.00 sec)

mysql>

[ACMAIN_CHM]

select a.id,a.name,b.name as pname
from folder a left join folder b on a.pid=b.id

[lfkcn]

多谢诸位，我还需要多学习，结贴