CREATE TABLE #t1 (id VARCHAR(10),name VARCHAR(100))
CREATE TABLE #t2 ( id VARCHAR(10), parentid VARCHAR(10), name VARCHAR(100) )
INSERT INTO #t1 ( id, name ) VALUES('1','asdfad');
INSERT INTO #t1 ( id, name ) VALUES('2','送达方');
INSERT INTO #t1 ( id, name ) VALUES('3','阿飞');
INSERT INTO #t1 ( id, name ) VALUES('4','可考虑');
INSERT INTO #t1 ( id, name ) VALUES('5','发放人');
INSERT INTO #t2 ( id,parentid, name ) VALUES('1','','1ff');
INSERT INTO #t2 ( id,parentid, name ) VALUES('2','','2上等');
INSERT INTO #t2 ( id,parentid, name ) VALUES('3','','3㐇');
INSERT INTO #t2 ( id,parentid, name ) VALUES('4','','4热');
INSERT INTO #t2 ( id,parentid, name ) VALUES('5','1','5发给');
INSERT INTO #t2 ( id,parentid, name ) VALUES('6','1','6是');
INSERT INTO #t2 ( id,parentid, name ) VALUES('7','1','7的');
INSERT INTO #t2 ( id,parentid, name ) VALUES('8','1','8飞');
INSERT INTO #t2 ( id,parentid, name ) VALUES('9','','9人');
INSERT INTO #t2 ( id,parentid, name ) VALUES('0','','10E店宝');
INSERT INTO #t2 ( id,parentid, name ) VALUES('11','5','11去');
INSERT INTO #t2 ( id,parentid, name ) VALUES('12','5','124 就');
INSERT INTO #t2 ( id,parentid, name ) VALUES('13','5','13 你');
INSERT INTO #t2 ( id,parentid, name ) VALUES('14','5','14了');
GO
SELECT a.id AS a_id,a.name AS a_name ,
b.id AS b_id,b.parentid AS b_parentid,b.name AS b_name,
c.id AS b_id,c.parentid AS c_parentid,c.name AS c_name
FROM #t1 AS a,
#t2 AS b
LEFT JOIN #t2 AS c ON b.parentid = c.id -- 子记录,使用父id 去关联父记录
WHERE a.id = b.id
