使用父ID连接行

Question

如果另一行引用父ID，是否有方法连接到行?

下面是我的例子:

+----+--------+-----------+
| Id | Title  | ParentID  |
+----+--------+-----------+
|  1 | AAAAAA | NULL      |
|  2 | BBBBBB | NULL      |
|  3 | CCCCCC | 1         |
|  4 | DDDDDD | 2         |
+----+--------+-----------+

预期输出

+----+--------------+
| Id | Title        | 
+----+--------------+
|  1 | AAAAAACCCCCC |  
|  2 | BBBBBBDDDDDD |
+----+--------------+

Answer 1

使用self join with id = parentID。从这个结果连接两个标题列。

例如:

select 
A.id, A.title || B.title as title 
from 
(select * from table) A 
inner join 
(select * from table) B on A.id = B.parentID

Answer 2

try this

create table test(id int, title varchar(10), ParentId Int);
insert into test (id, title, ParentId) Values (1, 'AAAAAA', null), (2, 'BBBBBB', null), (3, 'CCCCCC', 1), (4, 'DDDDDD', 2);
SELECT T1.Id, T1.title + '' + T2.title
FROM test T1
INNER JOIN test T2 ON T1.Id = T2.ParentId;

http://sqlfiddle.com/!18/c051fc/4