求一条SQL语句

[liushiying18]

表A

OrdersID	Discharge
201703-0001	NULL
201703-0002	NULL
201703-0003	NULL
201703-0004	201703-0001
201703-0005	201703-0002
201703-0006	201703-0001
201703-0007	201703-0002

想根据OrdersID=‘201703-0001’条件得到下面结果
201703-0001
201703-0004
201703-0006

OrdersID=‘201703-0004’
201703-0001
201703-0004
201703-0006

OrdersID=‘201703-0006’
201703-0001
201703-0004
201703-0006

[唐诗三百首]

create table 表A(OrdersID varchar(20),Discharge varchar(20))

insert into 表A(OrdersID,Discharge)
 select '201703-0001', null union all
 select '201703-0002', null union all
 select '201703-0003', null union all
 select '201703-0004', '201703-0001' union all
 select '201703-0005', '201703-0002' union all
 select '201703-0006', '201703-0001' union all
 select '201703-0007', '201703-0002'


create proc getOrdersIDList
(@OrdersID varchar(20))
as
begin
 with t as
 (select OrdersID,Discharge,lv=0 from 表A where OrdersID=@OrdersID
  union all
  select b.OrdersID,b.Discharge,a.lv+1
   from t a
   inner join 表A b on a.Discharge=b.OrdersID
   where a.lv>=0
  union all
  select b.OrdersID,b.Discharge,a.lv-1
   from t a
   inner join 表A b on a.OrdersID=b.Discharge
   where a.lv<=0
 )
 select OrdersID from t
 union
 select a.OrdersID
  from 表A a
  inner join t b on a.Discharge=b.Discharge
  where b.lv=0
end


-- 测试1
exec getOrdersIDList '201703-0001'

/*
OrdersID
--------------------
201703-0001
201703-0004
201703-0006

(3 row(s) affected)
*/


-- 测试2
exec getOrdersIDList '201703-0004'

/*
OrdersID
--------------------
201703-0001
201703-0004
201703-0006

(3 row(s) affected)
*/


-- 测试3
exec getOrdersIDList '201703-0006'

/*
OrdersID
--------------------
201703-0001
201703-0004
201703-0006

(3 row(s) affected)
*/

[卖水果的net]

这是什么规则？

[中国风]

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
declare @T table([OrdersID] nvarchar(31),[Discharge] nvarchar(31))
Insert @T
select N'201703-0001',null union all
select N'201703-0002',null union all
select N'201703-0003',null union all
select N'201703-0004',N'201703-0001' union all
select N'201703-0005',N'201703-0002' union all
select N'201703-0006',N'201703-0001' union all
select N'201703-0007',N'201703-0002'

;WITH Cte
AS
(
Select * from @T WHERE [OrdersID]=N'201703-0001'
UNION ALL
SELECT a.* FROM @T AS a INNER JOIN Cte AS c ON c.[OrdersID]=a.[Discharge]
)
SELECT Cte.OrdersID FROM Cte
/*
OrdersID
201703-0001
201703-0004
201703-0006
*/

[liushiying18]

SELECT OrdersID FROM Tb_Orders WHERE OrdersID=(SELECT isnull(Discharge,OrdersID) FROM Tb_Orders WHERE OrdersID='201703-0001' )
UNION
SELECT OrdersID FROM Tb_Orders WHERE Discharge=(SELECT isnull(Discharge,OrdersID) FROM Tb_Orders WHERE OrdersID='201703-0001' )

我是这些写的

[liushiying18]

引用 2 楼 roy_88 的回复:

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
declare @T table([OrdersID] nvarchar(31),[Discharge] nvarchar(31))
Insert @T
select N'201703-0001',null union all
select N'201703-0002',null union all
select N'201703-0003',null union all
select N'201703-0004',N'201703-0001' union all
select N'201703-0005',N'201703-0002' union all
select N'201703-0006',N'201703-0001' union all
select N'201703-0007',N'201703-0002'

;WITH Cte
AS
(
Select * from @T WHERE [OrdersID]=N'201703-0001'
UNION ALL
SELECT a.* FROM @T AS a INNER JOIN Cte AS c ON c.[OrdersID]=a.[Discharge]
)
SELECT Cte.OrdersID FROM Cte
/*
OrdersID
201703-0001
201703-0004
201703-0006
*/

这样不行啊OrdersID]='201703-0001'可以，但是当OrdersID]=N'201703-0004'时候查询结果不对

[顾西昂]

select distinct a.ordersid from A full outer join A b on A.ordersID=b.discharge and A.ordersID=b.Discharge where a.ordersid=@id or d.ordersid=@id

[中国风]

引用 4 楼 liushiying18 的回复:

这样不行啊OrdersID]='201703-0001'可以，但是当OrdersID]=N'201703-0004'时候查询结果不对

双向递归，这样用 看看效果是否正确 e.g.

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
declare @T table([OrdersID] nvarchar(31),[Discharge] nvarchar(31))
Insert @T
select N'201703-0001',null union all
select N'201703-0002',null union all
select N'201703-0003',null union all
select N'201703-0004',N'201703-0001' union all
select N'201703-0005',N'201703-0002' union all
select N'201703-0006',N'201703-0001' union all
select N'201703-0007',N'201703-0002'
;WITH Cte
AS
(
Select *,Type=0 from @T AS t WHERE EXISTS(SELECT 1 FROM @T WHERE  [OrdersID]=N'201703-0004' AND ([Discharge]=t.[Discharge] OR [OrdersID]=t.[OrdersID]))
UNION ALL
SELECT a.*,Type=1 FROM @T AS a INNER JOIN Cte AS c ON c.[OrdersID]=a.[Discharge] AND c.Type IN(0,1)
UNION ALL
SELECT a.*,Type=2 FROM @T AS a INNER JOIN Cte AS c ON a.[OrdersID]=c.[Discharge] AND c.Type IN(0,2)
)
SELECT DISTINCT Cte.OrdersID FROM Cte ORDER BY 1
/*
OrdersID
201703-0001
201703-0004
201703-0006
*/