表自己与自己连接的问题，麻烦帮忙写个sql

[todaytomorrow]

会议名称 会议时间 上届会议名称
会议0 2014.1.1 无
会议1 2015.1.1 会议0
会议2 2016.1.1 会议1
会议3 2017.1.1 会议2
会议A 2016.1.1 无
会议B 2017.1.1 会议A

要求输出
与会议2关联的所有会议
会议0 2014.1.1 无
会议1 2015.1.1 会议0
会议2 2016.1.1 会议1
会议3 2017.1.1 会议2

如果过于复杂，可以只输出会议2历届会议
会议0 2014.1.1 无
会议1 2015.1.1 会议0
会议2 2016.1.1 会议1


劳驾各位了。

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t([会议名称] nvarchar(20),[会议时间] datetime,[上届会议名称] nvarchar(20))
GO
INSERT INTO t
 SELECT '会议0','2014.1.1','无' 
 UNION SELECT '会议1','2015.1.1','会议0' 
 UNION SELECT '会议2','2016.1.1','会议1' 
 UNION SELECT '会议3','2017.1.1','会议2' 
 UNION SELECT '会议A','2016.1.1','无' 
 UNION SELECT '会议B','2017.1.1','会议A' 

;WITH cte AS (
SELECT * FROM t WHERE [会议名称]=N'会议2'
UNION ALL
SELECT t.* FROM t INNER JOIN cte ON t.[会议名称]=cte.[上届会议名称]
)
,cte2 AS (
SELECT * FROM t WHERE [会议名称]=N'会议2'
UNION ALL
SELECT t.* FROM t INNER JOIN cte2 ON t.[上届会议名称]=cte2.[会议名称]
)
SELECT * FROM cte
UNION 
SELECT * FROM cte2 ORDER BY [会议名称]
/*
会议名称                 会议时间                    上届会议名称
-------------------- ----------------------- --------------------
会议0                  2014-01-01 00:00:00.000 无
会议1                  2015-01-01 00:00:00.000 会议0
会议2                  2016-01-01 00:00:00.000 会议1
会议3                  2017-01-01 00:00:00.000 会议2
*/

[听雨停了]

use Tempdb
go
--> --> 听雨停了-->测试数据
 
if not object_id(N'Tempdb..#tab') is null
	drop table #tab
Go
Create table #tab([会议名称] nvarchar(23),[会议时间] Date,[上届会议名称] nvarchar(23))
Insert #tab
select N'会议0','2014.1.1',N'无' union all
select N'会议1','2015.1.1',N'会议0' union all
select N'会议2','2016.1.1',N'会议1' union all
select N'会议3','2017.1.1',N'会议2' union all
select N'会议A','2016.1.1',N'无' union all
select N'会议B','2017.1.1',N'会议A'
--测试数据结束

WITH cte AS (
SELECT * FROM #tab WHERE 上届会议名称='会议2'
UNION ALL
SELECT a.* FROM #tab A
INNER JOIN cte b ON a.会议名称=b.上届会议名称
)
SELECT * FROM cte
ORDER BY 会议名称

会议名称                    会议时间       上届会议名称
----------------------- ---------- -----------------------
会议0                     2014-01-01 无
会议1                     2015-01-01 会议0
会议2                     2016-01-01 会议1
会议3                     2017-01-01 会议2

[RINK_1]

 with cte
 as
 (select *,0 as level from table 
  where 会议名称='会议2'
  union all
  select A.*,B.level+1 from t A
  join cte B ON A.会议名称=B.上届会议名称
  where B.level>=0
  union all
  select A.*,C.level-1 from table A
  join cte C on A.上届会议名称=C.会议名称
  where C.level<=0)
  
select * from cte order by level desc

[顺势而为1]

就是以会议2作为父件号进行递降归查询就可以了

[道素]

下面写法和楼上差不多，第一步是向上找到第一次会议 然后再第一次为起点向下找，这个适用于你同时想把表亲也找出来的场合（比如本例的会议11，会议111非直系）

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
GO
CREATE TABLE #t(meeting nvarchar(20),meeting_time datetime,last_meeting nvarchar(20))
GO
INSERT INTO #t
 SELECT '会议0','2014.1.1','无' 
 UNION SELECT '会议1','2015.1.1','会议0' 
 UNION SELECT '会议2','2016.1.1','会议1' 
 UNION SELECT '会议11','2016.1.2','会议1' 
 UNION SELECT '会议111','2016.1.3','会议11' 
 UNION SELECT '会议3','2017.1.1','会议2' 
 UNION SELECT '会议A','2016.1.1','无' 
 UNION SELECT '会议B','2017.1.1','会议A' 
 
;WITH cte AS (
SELECT *,0 as lvl FROM #t WHERE meeting=N'会议2'
UNION ALL
SELECT t.*,lvl+1 as lvl FROM #t as t INNER JOIN cte ON t.meeting=cte.last_meeting
)
,cte2 AS (
SELECT top 1 * FROM cte order by lvl desc
UNION ALL
SELECT t.*,0 as lvl FROM #t as t INNER JOIN cte2 ON t.last_meeting=cte2.meeting
)
--SELECT top 1 * FROM cte order by lvl desc
SELECT * FROM cte2

+---------+-------------------------+--------------+-----+
| meeting | meeting_time            | last_meeting | lvl |
+---------+-------------------------+--------------+-----+
| 会议0     | 2014-01-01 00:00:00:000 | 无            | 2   |
| 会议1     | 2015-01-01 00:00:00:000 | 会议0          | 0   |
| 会议11    | 2016-01-02 00:00:00:000 | 会议1          | 0   |
| 会议2     | 2016-01-01 00:00:00:000 | 会议1          | 0   |
| 会议3     | 2017-01-01 00:00:00:000 | 会议2          | 0   |
| 会议111   | 2016-01-03 00:00:00:000 | 会议11         | 0   |
+---------+-------------------------+--------------+-----+